SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CONFIG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CONFIG](
	[NAME] [nvarchar](50) NULL,
	[VALUE] [nvarchar](250) NULL,
	[HOSPITAL_CODE] [nvarchar](10) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRN_DAILY_LOG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TRN_DAILY_LOG](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[INVOICE_NO] [nvarchar](30) NOT NULL,
	[INVOICE_DATE] [nvarchar](8) NOT NULL,
	[RECEIPT_NO] [nvarchar](20) NULL,
	[RECEIPT_DATE] [nvarchar](10) NULL,
	[TRANSACTION_DATE] [nvarchar](8) NOT NULL,
	[HN_NO] [nvarchar](20) NULL,
	[PATIENT_NAME] [nvarchar](255) NULL,
	[EPISODE_NO] [nvarchar](20) NULL,
	[NATIONALITY_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_LOG_NATIONALITY_CODE]  DEFAULT (''),
	[NATIONALITY_DESCRIPTION] [nvarchar](255) NULL CONSTRAINT [DF_TRN_DAILY_LOG_NATIONALITY_DESCRIPTION]  DEFAULT (''),
	[PAYOR_OFFICE_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_NAME] [nvarchar](255) NULL,
	[TRANSACTION_MODULE] [nvarchar](5) NULL,
	[TRANSACTION_TYPE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_DESCRIPTION] [nvarchar](255) NULL,
	[IS_WRITE_OFF] [nvarchar](2) NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NULL,
	[FIRST_DEPARTMENT_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_LOG_FIRST_DEPARTMENT_CODE]  DEFAULT (''),
	[FIRST_LOCATION_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_LOG_FIRST_LOCATION_CODE]  DEFAULT (''),
	[PATIENT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[PATIENT_LOCATION_CODE] [nvarchar](20) NULL,
	[RECEIPT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[RECEIPT_LOCATION_CODE] [nvarchar](20) NULL,
	[DOCTOR_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_DESCRIPTION] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[VERIFY_DATE] [nvarchar](8) NULL,
	[VERIFY_TIME] [nvarchar](6) NULL,
	[DOCTOR_EXECUTE_CODE] [nvarchar](20) NULL,
	[EXECUTE_DATE] [nvarchar](8) NULL,
	[EXECUTE_TIME] [nvarchar](6) NULL,
	[DOCTOR_RESULT_CODE] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NULL,
	[RECEIPT_TYPE_CODE] [nvarchar](20) NULL,
	[AMOUNT_BEF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_OF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_AFT_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_BEF_WRITE_OFF] [numeric](15, 2) NULL,
	[INV_IS_VOID] [nvarchar](2) NULL,
	[REC_IS_VOID] [nvarchar](2) NULL,
	[UPDATE_DATE] [nvarchar](8) NULL,
	[UPDATE_TIME] [nvarchar](6) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[BATCH_NO] [nvarchar](6) NULL,
	[YYYY] [nvarchar](8) NULL,
	[MM] [nvarchar](8) NULL,
	[DD] [nvarchar](8) NULL,
	[NOR_ALLOCATE_AMT] [numeric](15, 2) NULL,
	[NOR_ALLOCATE_PCT] [numeric](15, 2) NULL,
	[DR_AMT] [numeric](15, 2) NULL,
	[OLD_DR_AMT] [numeric](15, 2) NULL,
	[DR_TAX_400] [numeric](15, 2) NULL,
	[DR_TAX_401] [numeric](15, 2) NULL,
	[DR_TAX_402] [numeric](15, 2) NULL,
	[DR_TAX_406] [numeric](15, 2) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
	[DR_PREMIUM] [numeric](15, 2) NULL,
	[GUARANTEE_PAID_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_GUARANTEE_PAID_AMT]  DEFAULT ((0)),
	[GUARANTEE_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_GUARANTEE_AMT]  DEFAULT ((0)),
	[GUARANTEE_CODE] [nvarchar](20) NULL,
	[GUARANTEE_DR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_TYPE] [nvarchar](4) NULL,
	[GUARANTEE_DATE_TIME] [nvarchar](20) NULL,
	[GUARANTEE_TERM_MM] [nvarchar](2) NULL,
	[GUARANTEE_TERM_YYYY] [nvarchar](4) NULL,
	[GUARANTEE_NOTE] [nvarchar](50) NULL CONSTRAINT [DF_TRN_DAILY_LOG_GUARANTEE_NOTE]  DEFAULT (''),
	[IS_GUARANTEE] [nvarchar](2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_IS_GUARANTEE]  DEFAULT ('N'),
	[HP_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[COMPUTE_DAILY_DATE] [nvarchar](8) NULL,
	[COMPUTE_DAILY_TIME] [nvarchar](8) NULL,
	[COMPUTE_DAILY_USER_ID] [nvarchar](20) NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NULL,
	[EXCLUDE_TREATMENT] [nvarchar](20) NULL,
	[PREMIUM_CHARGE_PCT] [nvarchar](20) NULL,
	[PREMIUM_REC_AMT] [numeric](15, 2) NULL,
	[ACTIVE] [nvarchar](2) NULL,
	[INVOICE_TYPE] [nvarchar](20) NOT NULL CONSTRAINT [DF_TRN_DAILY_LOG_INVOICE_TYPE]  DEFAULT ('EXECUTE'),
	[TOTAL_BILL_AMOUNT] [numeric](15, 2) NULL,
	[TOTAL_DR_REC_AMOUNT] [numeric](15, 2) NULL,
	[OLD_AMOUNT] [numeric](15, 2) NULL,
	[PAY_BY_CASH] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PAY_BY_CASH]  DEFAULT ('N'),
	[PAY_BY_AR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PAY_BY_AR]  DEFAULT ('N'),
	[PAY_BY_DOCTOR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PAY_BY_DOCTOR]  DEFAULT ('N'),
	[PAY_BY_PAYOR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PAY_BY_PAYOR]  DEFAULT ('N'),
	[PAY_BY_CASH_AR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PAY_BY_CASH_AR]  DEFAULT ('N'),
	[IS_PAID] [nvarchar](1) NOT NULL CONSTRAINT [DF_TRN_DAILY_LOG_IS_PAID]  DEFAULT ('Y'),
	[ORDER_ITEM_ACTIVE] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_ORDER_ITEM_ACTIVE]  DEFAULT ('0'),
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NULL,
	[WRITE_OFF_BILL_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_WRITE_OFF_BILL_AMT]  DEFAULT ((0)),
	[WRITE_OFF_RECEIPT_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_WRITE_OFF_RECEIPT_AMT]  DEFAULT ((0)),
	[OLD_DR_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_OLD_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[DR_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[DR_PREMIUM_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_DR_PREMIUM_BEF_WRITE_OFF]  DEFAULT ((0)),
	[HP_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_HP_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[HP_PREMIUM_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_HP_PREMIUM_WRITE_OFF]  DEFAULT ((0)),
	[OLD_TAX_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_OLD_TAX_AMT]  DEFAULT ((0)),
	[DR_TAX_406_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF__TRN_DAILY_LOG__DR_TA__64B7E415]  DEFAULT ((0)),
	[TAX_FROM_ALLOCATE] [nvarchar](1) NULL CONSTRAINT [DF__TRN_DAILY_LOG__TAX_F__1431ED0D]  DEFAULT (''),
	[IS_GUARANTEE_FROM_ALLOC] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_LOG_IS_GUARANTEE_FROM_ALLOC]  DEFAULT (''),
	[PRIVATE_DOCTOR_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_LOG_PRIVATE_DOCTOR_CODE]  DEFAULT (''),
	[TAX_RATE] [numeric](5, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_TAX_RATE]  DEFAULT ((1)),
	[OLD_NOR_ALLOCATE_PCT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_LOG_OLD_NOR_ALLOCATE_PCT]  DEFAULT ((0))
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRN_EXPENSE_DETAIL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TRN_EXPENSE_DETAIL](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[EXPENSE_CODE] [nvarchar](20) NOT NULL,
	[EXPENSE_SIGN] [numeric](15, 0) NULL,
	[EXPENSE_ACCOUNT_CODE] [nvarchar](20) NULL,
	[AMOUNT] [numeric](15, 2) NOT NULL,
	[TAX_AMOUNT] [numeric](15, 2) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[SUPPLIER_CODE] [nvarchar](20) NULL,
	[INVOICE_TYPE_DESCRIPTION] [nvarchar](255) NULL,
	[DOC_NO] [nvarchar](20) NULL,
	[DOC_DATE] [nvarchar](10) NULL,
	[NOTE] [nvarchar](1500) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
	[COMPUTE_TAX_TYPE] [nvarchar](20) NULL,
	[DEPARTMENT_CODE] [nvarchar](20) NULL,
	[LOCATION_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_EXPENSE_DETAIL] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[DOCTOR_CODE] ASC,
	[HOSPITAL_CODE] ASC,
	[LINE_NO] ASC,
	[EXPENSE_CODE] ASC,
	[AMOUNT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GUARANTEE_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GUARANTEE_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_GUARANTEE_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_TIME_TABLE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_TIME_TABLE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[GUARANTEE_TYPE] [nvarchar](10) NOT NULL,
	[DAY] [nvarchar](10) NOT NULL,
	[START_TIME] [nvarchar](10) NOT NULL,
	[STOP_TIME] [nvarchar](10) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_STOP_TIME]  DEFAULT ('000000'),
	[GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_GUARANTEE_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_EXCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_TURN_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_FIX_AMOUNT] [numeric](15, 2) NULL DEFAULT ((0)),
	[GUARANTEE_INCLUDE_AMOUNT] [numeric](15, 2) NULL DEFAULT ((0)),
	[IN_GUARANTEE] [numeric](15, 2) NULL DEFAULT ((100)),
	[OVER_GUARANTEE] [numeric](15, 2) NULL DEFAULT ((100)),
	[USER_ID] [nchar](50) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_USER_ID]  DEFAULT (''),
	[UPDATE_DATE] [nchar](8) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_UPDATE_DATE]  DEFAULT (''),
	[UPDATE_TIME] [nchar](6) NULL CONSTRAINT [DF_DOCTOR_TIME_TABLE_UPDATE_TIME]  DEFAULT (''),
 CONSTRAINT [PK_DOCTOR_TIME_TABLE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[GUARANTEE_TYPE] ASC,
	[DAY] ASC,
	[START_TIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_GUARANTEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_GUARANTEE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[GUARANTEE_DR_CODE] [nvarchar](20) NOT NULL,
	[GUARANTEE_CODE] [nvarchar](20) NOT NULL,
	[GUARANTEE_TYPE_CODE] [nvarchar](20) NOT NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_TYPE_CODE]  DEFAULT (N'DLY'),
	[IS_GUARANTEE_DAILY] [nvarchar](1) NULL CONSTRAINT [DF_STP_GUARANTEE_IS_GUARANTEE_DAILY]  DEFAULT ('N'),
	[GUARANTEE_LOCATION] [nvarchar](20) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_LOCATION]  DEFAULT (' '),
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[YYYY] [nvarchar](4) NOT NULL,
	[START_DATE] [nvarchar](10) NOT NULL,
	[START_TIME] [nvarchar](10) NOT NULL,
	[EARLY_TIME] [nvarchar](10) NULL CONSTRAINT [DF_STP_GUARANTEE_EARLY_TIME]  DEFAULT ('000000'),
	[END_DATE] [nvarchar](10) NOT NULL,
	[END_TIME] [nvarchar](10) NOT NULL,
	[LATE_TIME] [nvarchar](10) NULL CONSTRAINT [DF_STP_GUARANTEE_LATE_TIME]  DEFAULT ('000000'),
	[GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_FIX_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_FIX_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_INCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_INCLUDE_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_EXCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_EXCLUDE_AMOUNT]  DEFAULT ((0)),
	[OVER_ALLOCATE_PCT] [numeric](15, 0) NULL,
	[GUARANTEE_ALLOCATE_PCT] [numeric](15, 0) NULL,
	[ACTIVE] [nvarchar](50) NULL CONSTRAINT [DF_STP_GUARANTEE_ACTIVE]  DEFAULT ((1)),
	[UPDATE_DATE] [nvarchar](50) NULL,
	[UPDATE_TIME] [nvarchar](50) NULL,
	[USER_ID] [nvarchar](50) NULL,
	[DF406_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF406_CASH_AMOUNT]  DEFAULT ((0)),
	[DF406_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF406_CREDIT_AMOUNT]  DEFAULT ((0)),
	[DF406_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF406_HOLD_AMOUNT]  DEFAULT ((0)),
	[DF402_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF402_CASH_AMOUNT]  DEFAULT ((0)),
	[DF402_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF402_CREDIT_AMOUNT]  DEFAULT ((0)),
	[DF402_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF402_HOLD_AMOUNT]  DEFAULT ((0)),
	[DF400_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF400_CASH_AMOUNT]  DEFAULT ((0)),
	[DF400_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF400_CREDIT_AMOUNT]  DEFAULT ((0)),
	[DF400_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF400_HOLD_AMOUNT]  DEFAULT ((0)),
	[DF_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF_CASH_AMOUNT]  DEFAULT ((0)),
	[DF_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF_HOLD_AMOUNT]  DEFAULT ((0)),
	[HP402_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_HP402_ABSORB_AMOUNT]  DEFAULT ((0)),
	[DF406_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF406_ABSORB_AMOUNT]  DEFAULT ((0)),
	[DF402_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF402_ABSORB_AMOUNT]  DEFAULT ((0)),
	[DF400_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF400_ABSORB_AMOUNT]  DEFAULT ((0)),
	[DF_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_DF_ABSORB_AMOUNT]  DEFAULT ((0)),
	[OVER_GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OVER_GUARANTEE_AMOUNT]  DEFAULT ((0)),
	[SUM_DR_OVER_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_SUM_DR_OVER_AMOUNT]  DEFAULT ((0)),
	[SUM_HP_OVER_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_SUM_HP_OVER_AMOUNT]  DEFAULT ((0)),
	[GUARANTEE_PAID_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_GUARANTEE_PAID_AMOUNT]  DEFAULT ((0)),
	[SUM_TAX_406] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_SUM_TAX_406]  DEFAULT ((0)),
	[SUM_TAX_402] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_SUM_TAX_402]  DEFAULT ((0)),
	[SUM_TAX_400] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_SUM_TAX_400]  DEFAULT ((0)),
	[OLD_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_ABSORB_AMOUNT]  DEFAULT ((0)),
	[OLD_GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_GUARANTEE_AMOUNT]  DEFAULT ((0)),
	[OLD_GUARANTEE_FIX_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_GUARANTEE_FIX_AMOUNT]  DEFAULT ((0)),
	[OLD_GUARANTEE_INCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_GUARANTEE_INCLUDE_AMOUNT]  DEFAULT ((0)),
	[OLD_GUARANTEE_EXCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_GUARANTEE_EXCLUDE_AMOUNT]  DEFAULT ((0)),
	[OLD_ACTIVE] [char](1) NULL CONSTRAINT [DF_STP_GUARANTEE_OLD_ACTIVE]  DEFAULT ('1'),
	[OLD_START_DATE] [nvarchar](8) NULL CONSTRAINT [DF__STP_GUARA__OLD_S__65AC084E]  DEFAULT (''),
	[OLD_END_DATE] [nvarchar](8) NULL CONSTRAINT [DF__STP_GUARA__OLD_E__66A02C87]  DEFAULT (''),
	[GUARANTEE_DAY] [nvarchar](3) NULL CONSTRAINT [DF__STP_GUARA__GUARA__0F6D37F0]  DEFAULT (''),
	[NOTE] [nvarchar](255) NULL CONSTRAINT [DF__STP_GUARAN__NOTE__18F6A22A]  DEFAULT (''),
	[GUARANTEE_SOURCE] [nvarchar](4) NULL CONSTRAINT [DF__STP_GUARA__GUARA__2B155265]  DEFAULT (''),
	[OLD_GUARANTEE_SOURCE] [nvarchar](4) NULL CONSTRAINT [DF__STP_GUARA__OLD_G__2C09769E]  DEFAULT (''),
	[HP_ABSORB_DOCTOR_CODE] [nvarchar](50) COLLATE Thai_BIN NULL,
	[AMOUNT_OF_TIME] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_AMOUNT_OF_TIME]  DEFAULT ((0)),
	[AMOUNT_DIFF_TIME] [numeric](15, 2) NULL CONSTRAINT [DF_STP_GUARANTEE_AMOUNT_DIFF_TIME]  DEFAULT ((0)),
	[IS_PROCESS] [nvarchar](1) NULL CONSTRAINT [DF_STP_GUARANTEE_IS_PROCESS]  DEFAULT ('N'),
 CONSTRAINT [PK_STP_GUARANTEE_1] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[GUARANTEE_DR_CODE] ASC,
	[GUARANTEE_CODE] ASC,
	[GUARANTEE_TYPE_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[MM] ASC,
	[YYYY] ASC,
	[START_DATE] ASC,
	[START_TIME] ASC,
	[END_DATE] ASC,
	[END_TIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_GUARANTEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_GUARANTEE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[GUARANTEE_CODE] [nvarchar](20) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__3F115E1A]  DEFAULT (NULL),
	[GUARANTEE_DR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_LOCATION] [nvarchar](20) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__40058253]  DEFAULT (NULL),
	[ADMISSION_TYPE_CODE] [nvarchar](1) NULL CONSTRAINT [DF__SUMMARY_G__ADMIS__40F9A68C]  DEFAULT (NULL),
	[MM] [nvarchar](2) NOT NULL CONSTRAINT [DF__SUMMARY_GUAR__MM__41EDCAC5]  DEFAULT (NULL),
	[YYYY] [nvarchar](4) NOT NULL CONSTRAINT [DF__SUMMARY_GU__YYYY__42E1EEFE]  DEFAULT (NULL),
	[GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__43D61337]  DEFAULT ('0.00'),
	[GUARANTEE_INCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__44CA3770]  DEFAULT ('0.00'),
	[GUARANTEE_EXCLUDE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__45BE5BA9]  DEFAULT ('0.00'),
	[GUARANTEE_FIX_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__46B27FE2]  DEFAULT ('0.00'),
	[DF406_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF406__47A6A41B]  DEFAULT ('0.00'),
	[DF406_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF406__489AC854]  DEFAULT ('0.00'),
	[DF402_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF402__498EEC8D]  DEFAULT ('0.00'),
	[DF402_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF402__4A8310C6]  DEFAULT ('0.00'),
	[DF400_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF400__4B7734FF]  DEFAULT ('0.00'),
	[DF400_CREDIT_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF400__4C6B5938]  DEFAULT ('0.00'),
	[DF406_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF406__4D5F7D71]  DEFAULT ('0.00'),
	[DF402_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF402__4E53A1AA]  DEFAULT ('0.00'),
	[DF400_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF400__4F47C5E3]  DEFAULT ('0.00'),
	[DF_CASH_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF_CA__503BEA1C]  DEFAULT ('0.00'),
	[DF_HOLD_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF_HO__51300E55]  DEFAULT ('0.00'),
	[DF406_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF406__5224328E]  DEFAULT ('0.00'),
	[DF402_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF402__531856C7]  DEFAULT ('0.00'),
	[DF400_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF400__540C7B00]  DEFAULT ('0.00'),
	[DF_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__DF_AB__55009F39]  DEFAULT ('0.00'),
	[HP402_ABSORB_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__HP402__55F4C372]  DEFAULT ('0.00'),
	[OVER_GUARANTEE_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__OVER___56E8E7AB]  DEFAULT ('0.00'),
	[SUM_DR_OVER_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__SUM_D__57DD0BE4]  DEFAULT ('0.00'),
	[SUM_HP_OVER_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__SUM_H__58D1301D]  DEFAULT ('0.00'),
	[GUARANTEE_PAID_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__GUARA__59C55456]  DEFAULT ('0.00'),
	[SUM_TAX_406] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__SUM_T__5AB9788F]  DEFAULT ('0.00'),
	[SUM_TAX_402] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__SUM_T__5BAD9CC8]  DEFAULT ('0.00'),
	[SUM_TAX_400] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_G__SUM_T__5CA1C101]  DEFAULT ('0.00'),
	[BATCH_NO] [nvarchar](10) NULL,
 CONSTRAINT [PK_SUMMARY_GUARANTEE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[MM] ASC,
	[YYYY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ACCOUNT](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
 CONSTRAINT [PK_ACCOUNT] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_USERS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_USERS](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[LOGIN_NAME] [nvarchar](50) NOT NULL,
	[PASSWORD] [nvarchar](32) NOT NULL,
	[NAME] [nvarchar](255) NULL,
	[USER_GROUP_CODE] [numeric](2, 0) NULL,
	[ACTIVE] [nvarchar](50) NULL,
	[LOGIN_DATE] [nvarchar](50) NULL,
	[LOGIN_TIME] [nvarchar](50) NULL,
	[IPADDRESS] [nvarchar](50) NULL,
	[EMAIL] [nvarchar](100) NULL,
	[LANG_CODE] [nchar](1) NULL,
 CONSTRAINT [PK_STP_USERS] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[LOGIN_NAME] ASC,
	[PASSWORD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_MENU]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_MENU](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[CODE] [nvarchar](50) NOT NULL,
	[MENU_THAI] [nvarchar](255) NULL,
	[MENU_ENG] [nvarchar](255) NULL,
	[LINK_PAGE] [nvarchar](255) NULL,
	[PARENT_CODE] [nvarchar](50) NULL,
	[STATUS] [numeric](2, 0) NULL,
	[ICON_LINK] [nvarchar](255) NULL,
	[ICON_PICTURE] [nvarchar](255) NULL,
	[ICON_DESCRIPTION] [nvarchar](255) NULL,
 CONSTRAINT [PK_STP_MENU] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADMISSION_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ADMISSION_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_ADMISSION_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_PROFILE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_PROFILE](
	[CODE] [nvarchar](20) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NULL,
	[NAME_THAI] [nvarchar](255) NULL,
	[NAME_ENG] [nvarchar](255) NULL,
	[ADDRESS1] [nvarchar](255) NULL,
	[ADDRESS2] [nvarchar](255) NULL,
	[ADDRESS3] [nvarchar](255) NULL,
	[ZIP] [nvarchar](10) NULL,
	[PLACE_OF_WORK] [nvarchar](255) NULL,
	[BIRTH_DATE] [nvarchar](10) NULL,
	[NATION_ID] [nvarchar](20) NULL,
	[FROM_DATE] [nvarchar](10) NULL,
	[TO_DATE] [nvarchar](10) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[POSITION] [nvarchar](255) NULL,
	[BRANCH] [nvarchar](255) NULL,
	[SUB_BRANCH] [nvarchar](255) NULL,
	[INSURRANCE_NAME] [nvarchar](255) NULL,
	[INSURRANCE_NO] [nvarchar](20) NULL,
	[INSURRANCE_YEAR] [nvarchar](10) NULL,
	[INSURRANCE_MONEY] [numeric](15, 2) NULL,
	[HP_REF_NAME_01] [nvarchar](255) NULL,
	[HP_REF_BRANCH_01] [nvarchar](255) NULL,
	[HP_REF_YEAR_01] [nvarchar](10) NULL,
	[HP_REF_NAME_02] [nvarchar](255) NULL,
	[HP_REF_BRANCH_02] [nvarchar](255) NULL,
	[HP_REF_YEAR_02] [nvarchar](10) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[UNDER_HOSPITAL] [nvarchar](100) NULL,
	[UNDER_HOSPITAL_BRANCH] [nvarchar](100) NULL,
	[MEMO] [nvarchar](400) NULL,
	[LANGUAGE] [nvarchar](100) NULL,
	[SPECIALTY] [nvarchar](255) NULL,
	[SUB_SPECIALTY] [nvarchar](255) NULL,
	[DEPARTMENT_CODE] [nvarchar](20) NULL,
	[LICENSE_ID] [nvarchar](30) NULL,
	[LICENSE_EXPIRE_DATE] [nvarchar](10) NULL,
 CONSTRAINT [PK_DOCTOR_GROUP] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BANK_BRANCH]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BANK_BRANCH](
	[CODE] [nvarchar](20) NOT NULL,
	[BANK_CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_BANK_BRANCH] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[BANK_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_ERP_AR_RECEIPT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_ERP_AR_RECEIPT](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[BILL_NO] [nvarchar](20) NOT NULL,
	[RECEIPT_NO] [nvarchar](20) NOT NULL,
	[RECEIPT_DATE] [nvarchar](10) NOT NULL,
	[RECEIPT_TYPE_CODE] [nvarchar](20) NULL,
	[BILL_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_NOTE_AMOUNT] [numeric](15, 2) NULL,
	[DEBIT_NOTE_AMOUNT] [numeric](15, 2) NULL,
	[PAYMENT_AMOUNT] [numeric](15, 2) NOT NULL,
	[WRITE_OFF_AMOUNT] [numeric](15, 2) NULL,
	[DOC_TYPE] [nvarchar](1) NULL,
	[IS_LAST_RECEIPT] [nvarchar](1) NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](10) NULL,
	[IS_LOADED] [nvarchar](1) NULL,
 CONSTRAINT [PK_INT_ERP_AR_RECEIPT] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[BILL_NO] ASC,
	[RECEIPT_NO] ASC,
	[RECEIPT_DATE] ASC,
	[PAYMENT_AMOUNT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BANK]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BANK](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_BANK] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REVENUE_SHARE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[REVENUE_SHARE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[INVOICE_NO] [nvarchar](30) NOT NULL,
	[GROUP_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_PROFILE_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](50) NOT NULL,
	[AMOUNT_AFT_DISCOUNT] [numeric](15, 2) NOT NULL,
	[DR_AMT] [numeric](15, 2) NULL,
	[EXPENSE_AMT] [numeric](15, 2) NULL,
	[DF_PERCENT] [numeric](15, 2) NULL,
	[PERCENT_DF] [numeric](15, 2) NULL,
	[PERCENT_POOL] [numeric](15, 2) NULL,
	[PERCENT_CK] [numeric](15, 2) NULL,
	[AMOUNT_DF] [numeric](15, 2) NULL,
	[AMOUNT_POOL] [numeric](15, 2) NULL,
	[AMOUNT_CK] [numeric](15, 2) NULL,
	[NOTE] [varchar](255) NULL,
	[CREATE_DATE] [nvarchar](10) NULL,
	[CREATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_REVENUE_SHARE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC,
	[LINE_NO] ASC,
	[INVOICE_NO] ASC,
	[GROUP_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[ORDER_ITEM_CATEGORY_CODE] ASC,
	[DOCTOR_PROFILE_CODE] ASC,
	[DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BATCH]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BATCH](
	[BATCH_NO] [nvarchar](20) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[YYYY] [nvarchar](4) NULL,
	[MM] [nvarchar](2) NULL,
	[CREATE_DATE] [nvarchar](10) NULL,
	[CREATE_TIME] [nvarchar](10) NULL,
	[CLOSE_DATE] [nvarchar](10) NULL,
	[CLOSE_TIME] [nvarchar](10) NULL,
	[CREATE_BY_USER_ID] [nvarchar](20) NULL,
	[CLOSE_BY_USER_ID] [nvarchar](20) NULL,
	[PAYMENT_DATE] [nvarchar](10) NULL,
 CONSTRAINT [PK_BATCH] PRIMARY KEY CLUSTERED 
(
	[BATCH_NO] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REVENUE_SHARE_PRORATE_DOCTOR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[REVENUE_SHARE_PRORATE_DOCTOR](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[GROUP_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](50) NOT NULL,
	[TYPE] [nvarchar](4) NOT NULL,
	[PRORATE_AMOUNT] [numeric](15, 2) NULL,
	[CREATE_DATE] [nvarchar](10) NULL,
	[CREATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_REVENUE_SHARE_PRORATE_DOCTOR] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC,
	[GROUP_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_DAILY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_DAILY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[INVOICE_NO] [nvarchar](30) NOT NULL,
	[INVOICE_DATE] [nvarchar](8) NOT NULL,
	[RECEIPT_NO] [nvarchar](20) NULL,
	[RECEIPT_DATE] [nvarchar](10) NULL,
	[TRANSACTION_DATE] [nvarchar](8) NOT NULL,
	[HN_NO] [nvarchar](20) NULL,
	[PATIENT_NAME] [nvarchar](255) NULL,
	[EPISODE_NO] [nvarchar](20) NULL,
	[NATIONALITY_CODE] [nvarchar](20) NULL CONSTRAINT [DF_SUMMARY_DAILY_NATIONALITY_CODE]  DEFAULT (''),
	[NATIONALITY_DESCRIPTION] [nvarchar](255) NULL CONSTRAINT [DF_SUMMARY_DAILY_NATIONALITY_DESCRIPTION]  DEFAULT (''),
	[PAYOR_OFFICE_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_NAME] [nvarchar](255) NULL,
	[TRANSACTION_MODULE] [nvarchar](5) NULL,
	[TRANSACTION_TYPE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_DESCRIPTION] [nvarchar](255) NULL,
	[IS_WRITE_OFF] [nvarchar](2) NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NULL,
	[PATIENT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[PATIENT_LOCATION_CODE] [nvarchar](20) NULL,
	[RECEIPT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[RECEIPT_LOCATION_CODE] [nvarchar](20) NULL,
	[DOCTOR_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_DESCRIPTION] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[VERIFY_DATE] [nvarchar](8) NULL,
	[VERIFY_TIME] [nvarchar](6) NULL,
	[DOCTOR_EXECUTE_CODE] [nvarchar](20) NULL,
	[EXECUTE_DATE] [nvarchar](8) NULL,
	[EXECUTE_TIME] [nvarchar](6) NULL,
	[DOCTOR_RESULT_CODE] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NULL,
	[RECEIPT_TYPE_CODE] [nvarchar](20) NULL,
	[AMOUNT_BEF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_OF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_AFT_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_BEF_WRITE_OFF] [numeric](15, 2) NULL,
	[INV_IS_VOID] [nvarchar](2) NOT NULL,
	[REC_IS_VOID] [nvarchar](2) NOT NULL,
	[UPDATE_DATE] [nvarchar](8) NULL,
	[UPDATE_TIME] [nvarchar](6) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[BATCH_NO] [nvarchar](6) NULL,
	[YYYY] [nvarchar](8) NULL,
	[MM] [nvarchar](8) NULL,
	[DD] [nvarchar](8) NULL,
	[NOR_ALLOCATE_AMT] [numeric](15, 2) NULL,
	[NOR_ALLOCATE_PCT] [numeric](15, 2) NULL,
	[DR_AMT] [numeric](15, 2) NULL,
	[DR_TAX_400] [numeric](15, 2) NULL,
	[DR_TAX_401] [numeric](15, 2) NULL,
	[DR_TAX_402] [numeric](15, 2) NULL,
	[DR_TAX_406] [numeric](15, 2) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
	[DR_PREMIUM] [numeric](15, 2) NULL,
	[GUARANTEE_AMT] [numeric](15, 2) NULL,
	[GUARANTEE_CODE] [nvarchar](20) NULL,
	[GUARANTEE_DR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_TYPE] [nvarchar](4) NULL,
	[GUARANTEE_DATE_TIME] [nvarchar](20) NULL,
	[GUARANTEE_TERM_MM] [nvarchar](2) NULL,
	[GUARANTEE_TERM_YYYY] [nvarchar](4) NULL,
	[IS_GUARANTEE] [nvarchar](2) NULL,
	[HP_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[COMPUTE_DAILY_DATE] [nvarchar](8) NULL,
	[COMPUTE_DAILY_TIME] [nvarchar](8) NULL,
	[COMPUTE_DAILY_USER_ID] [nvarchar](20) NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NULL,
	[EXCLUDE_TREATMENT] [nvarchar](20) NULL,
	[PREMIUM_CHARGE_PCT] [nvarchar](20) NULL,
	[PREMIUM_REC_AMT] [numeric](15, 2) NULL,
	[ACTIVE] [nvarchar](2) NULL,
	[INVOICE_TYPE] [nvarchar](2) NOT NULL,
	[TOTAL_BILL_AMOUNT] [numeric](15, 2) NULL,
	[TOTAL_DR_REC_AMOUNT] [numeric](15, 2) NULL,
	[OLD_AMOUNT] [numeric](15, 2) NULL,
	[PAY_BY_CASH] [nvarchar](1) NULL,
	[PAY_BY_AR] [nvarchar](1) NULL,
	[PAY_BY_DOCTOR] [nvarchar](1) NULL,
	[PAY_BY_PAYOR] [nvarchar](1) NULL,
	[PAY_BY_CASH_AR] [nvarchar](1) NULL,
	[IS_PAID] [nvarchar](1) NOT NULL CONSTRAINT [DF_SUMMARY_DAILY_IS_PAID]  DEFAULT ('Y'),
 CONSTRAINT [PK_SUMMARY_DAILY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[INVOICE_NO] ASC,
	[INVOICE_DATE] ASC,
	[TRANSACTION_DATE] ASC,
	[LINE_NO] ASC,
	[INV_IS_VOID] ASC,
	[REC_IS_VOID] ASC,
	[INVOICE_TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LOCATION]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LOCATION](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[BUILDING] [nvarchar](10) NULL,
	[FLOOR] [nvarchar](10) NULL,
	[TEL] [nvarchar](20) NULL,
	[FROM_DATE] [nvarchar](10) NULL,
	[TO_DATE] [nvarchar](10) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DEPARTMENT_CODE] [nvarchar](20) NOT NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[LOCATION_TYPE_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_LOCATION] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC,
	[DEPARTMENT_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_ABSORB]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_ABSORB](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL CONSTRAINT [DF__SUMMARY_A__HOSPI__7FEAFD3E]  DEFAULT (NULL),
	[DOCTOR_CODE] [nvarchar](20) NOT NULL CONSTRAINT [DF__SUMMARY_A__DOCTO__00DF2177]  DEFAULT (NULL),
	[MM] [nvarchar](2) NOT NULL CONSTRAINT [DF__SUMMARY_ABSO__MM__01D345B0]  DEFAULT (NULL),
	[YYYY] [nvarchar](4) NOT NULL CONSTRAINT [DF__SUMMARY_AB__YYYY__02C769E9]  DEFAULT (NULL),
	[ABSORB_DATE] [nvarchar](50) NULL,
	[ACCU_ABSORB_TAX_400] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_ABSORB_ACCU_ABSORB_TAX_4021]  DEFAULT ('0.00'),
	[ACCU_ABSORB_TAX_402] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_A__ACCU___03BB8E22]  DEFAULT ('0.00'),
	[ACCU_ABSORB_TAX_406] [numeric](15, 2) NULL CONSTRAINT [DF__SUMMARY_A__ACCU___04AFB25B]  DEFAULT ('0.00'),
 CONSTRAINT [PK_SUMMARY_ABSORB] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[MM] ASC,
	[YYYY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CHANGE_DOCTOR_CODE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CHANGE_DOCTOR_CODE](
	[DATE_FROM] [nvarchar](8) NOT NULL,
	[DATE_TO] [nvarchar](8) NOT NULL,
	[DOCTOR_CODE_FROM] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE_TO] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE] [nvarchar](20) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[YYYY] [nvarchar](4) NOT NULL,
	[USER_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[INVOICE_TYPE] [nvarchar](10) NULL,
 CONSTRAINT [PK_CHANGE_DOCTOR_CODE] PRIMARY KEY CLUSTERED 
(
	[DOCTOR_CODE_FROM] ASC,
	[DOCTOR_CODE_TO] ASC,
	[MM] ASC,
	[YYYY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_DISTRIBUTE_REVENUE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_DISTRIBUTE_REVENUE](
	[HOSPITAL_CODE] [nvarchar](20) NULL,
	[DOCTOR_PROFILE_CODE] [nvarchar](20) NULL,
	[DISTRIBUTE_TYPE] [nvarchar](1) NULL,
	[REVENUE_TYPE] [nvarchar](1) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[DISTRIBUTE_PERCENT] [numeric](15, 2) NULL,
	[DISTRIBUTE_AMOUNT] [numeric](15, 2) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAYOR_OFFICE_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PAYOR_OFFICE_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[NAME_THAI] [nvarchar](255) NULL,
	[NAME_ENG] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_PAYOR_OFFICE_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_GROUP]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_GROUP](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[GROUP_NAME_THAI] [nvarchar](255) NULL,
	[GROUP_NAME_ENG] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](50) NULL,
	[UPDATE_TIME] [nvarchar](50) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_GROUP] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LOCATION_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LOCATION_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_LOCATION_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_MONTHLY_STEP]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_MONTHLY_STEP](
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[YYYY] [nvarchar](10) NOT NULL,
	[MM] [nvarchar](10) NOT NULL,
	[STEP_SHARING_CODE] [nvarchar](20) NOT NULL,
	[SORDER] [numeric](15, 0) NULL,
	[DR_PERCENT] [numeric](15, 2) NULL,
	[HP_PERCENT] [numeric](15, 2) NULL,
	[DR_AMT] [numeric](15, 2) NULL,
	[HP_AMT] [numeric](15, 2) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_SUMMARY_MONTHLY_STEP_1] PRIMARY KEY CLUSTERED 
(
	[DOCTOR_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC,
	[STEP_SHARING_CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_TAX_402]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_TAX_402](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[POSITION_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_POSITION_AMT]  DEFAULT ((0)),
	[TURN_IN_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_TURN_IN_AMT]  DEFAULT ((0)),
	[TURN_OUT_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_TURN_OUT_AMT]  DEFAULT ((0)),
	[GUARANTEE_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_GUARANTEE_AMT]  DEFAULT ((0)),
	[OTHER_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_OTHER_AMT]  DEFAULT ((0)),
	[SUM_NORMAL_TAX_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_SUM_NORMAL_TAX_AMT]  DEFAULT ((0)),
	[SUM_TURN_TAX_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_SUM_TURN_TAX_AMT]  DEFAULT ((0)),
	[NORMAL_TAX_MONTH] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_NORMAL_TAX_MONTH]  DEFAULT ((0)),
	[ACCU_NORMAL_TAX_MONTH] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_ACCU_NORMAL_TAX_MONTH]  DEFAULT ((0)),
	[HOSPITAL_TAX_MONTH] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_HOSPITAL_TAX_MONTH]  DEFAULT ((0)),
	[ACCU_HOSPITAL_TAX_MONTH] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_ACCU_HOSPITAL_TAX_MONTH]  DEFAULT ((0)),
	[NET_TAX_MONTH] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_TAX_402_NET_TAX_MONTH]  DEFAULT ((0)),
	[TEXT_NET_TAX_MONTH] [nvarchar](255) NULL CONSTRAINT [DF_SUMMARY_TAX_402_TEXT_NET_TAX_MONTH]  DEFAULT ((0)),
	[BATCH_NO] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[STATUS_MODIFY] [nvarchar](10) NULL,
	[ACTIVE] [char](1) NULL CONSTRAINT [DF_SUMMARY_TAX_402_ACTIVE]  DEFAULT ('1'),
	[TAX_TERM] [nvarchar](6) NULL CONSTRAINT [DF_SUMMARY_TAX_402_TAX_TERM]  DEFAULT (''),
	[LINE_NO] [nvarchar](4) NULL CONSTRAINT [DF_SUMMARY_TAX_402_LINE_NO]  DEFAULT ('0000'),
 CONSTRAINT [PK_SUMMARY_TAX_402] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[DOCTOR_CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_PERSONAL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_PERSONAL](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[PRICE] [numeric](15, 2) NOT NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NOT NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NOT NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NOT NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NOT NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_PERSONAL_IS_ALLOC_FULL_TAX]  DEFAULT (''),
	[TAX_RATE] [numeric](15, 2) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_PERSONAL_TAX_RATE]  DEFAULT ((1)),
	[IS_PRIVATE] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_PERSONAL_IS_PRIVATE]  DEFAULT ('N'),
	[GUARANTEE_SOURCE] [nvarchar](10) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_PERSONAL_GUARANTEE_SOURCE]  DEFAULT ('NONE'),
	[REMARK] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NOT NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_METHOD_ALLOC_PERSONAL] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[ORDER_ITEM_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[PRICE] ASC,
	[TAX_TYPE_CODE] ASC,
	[EXCLUDE_TREATMENT] ASC,
	[NORMAL_ALLOCATE_PCT] ASC,
	[NORMAL_ALLOCATE_AMT] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_CLASS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_CLASS](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[CLASS_NAME] [nvarchar](50) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_DOCTOR_CLASS] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_GROUP_DOCTOR_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_GROUP_DOCTOR_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[GROUP_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](255) NOT NULL,
	[DF] [numeric](6, 2) NULL,
	[POOL] [numeric](6, 2) NULL,
	[CK] [numeric](6, 2) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_GROUP_DOCTOR_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[GROUP_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_HIS_BILL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_HIS_BILL](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[EPISODE_TYPE] [nvarchar](1) NULL,
	[BILL_NO] [nvarchar](30) NOT NULL,
	[BILL_DATE] [nvarchar](50) NOT NULL,
	[RECEIPT_TYPE_CODE] [nvarchar](20) NULL,
	[TRANSACTION_TYPE] [nvarchar](10) NULL,
	[HN_NO] [nvarchar](20) NULL,
	[PATIENT_NAME] [nvarchar](255) NULL,
	[EPISODE_NO] [nvarchar](20) NULL,
	[PAYOR_CODE] [nvarchar](20) NULL,
	[PAYOR_NAME] [nvarchar](255) NULL,
	[PAYOR_CATEGORY_CODE] [nvarchar](20) NULL,
	[PAYOR_CATEGORY_DESC] [nvarchar](255) NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](1) NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_DESCRIPTION] [nvarchar](255) NULL,
	[DOCTOR_PROFILE_CODE] [nvarchar](20) NULL,
	[DOCTOR_PROFILE_NAME] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[DOCTOR_NAME] [nvarchar](255) NULL,
	[DOCTOR_PRIVATE] [nvarchar](20) NULL,
	[AMOUNT_BEF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_OF_DISCOUNT] [numeric](15, 2) NULL,
	[OLD_AMOUNT] [numeric](15, 2) NULL,
	[ORDERED_DATE] [nvarchar](50) NULL,
	[ORDERED_TIME] [nvarchar](50) NULL,
	[NATIONALITY_CODE] [nvarchar](20) NULL,
	[NATIONALITY_DESCRIPTION] [nvarchar](255) NULL,
	[FIRST_LOCATION_CODE] [nvarchar](20) NULL,
	[FIRST_LOCATION_DESC] [nvarchar](255) NULL,
	[FIRST_LOCATION_DEPT_CODE] [nvarchar](20) NULL,
	[FIRST_LOCATION_DEPT_DESC] [nvarchar](255) NULL,
	[PATIENT_LOCATION_CODE] [nvarchar](20) NULL,
	[PATIENT_LOCATION_DESC] [nvarchar](255) NULL,
	[PATIENT_LOCATION_DEPT_CODE] [nvarchar](20) NULL,
	[PATIENT_LOCATION_DEPT_DESC] [nvarchar](255) NULL,
	[RECEIVING_LOCATION_CODE] [nvarchar](20) NULL,
	[RECEIVING_LOCATION_DESC] [nvarchar](255) NULL,
	[RECEIVING_LOCATION_DEPT_CODE] [nvarchar](20) NULL,
	[RECEIVING_LOCATION_DEPT_DESC] [nvarchar](255) NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[SPECIALTY_TYPE] [nvarchar] (10) NULL,
	[VERIFIED_DATE] [nvarchar](10) NULL,
	[VERIFIED_TIME] [nvarchar](10) NULL,
	[BILL_TOTAL_AMOUNT] [numeric](15, 2) NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
	[INVOICE_TYPE] [nvarchar](20) NULL,
 CONSTRAINT [PK_INT_HIS_BILL] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[BILL_NO] ASC,
	[BILL_DATE] ASC,
	[LINE_NO] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRN_EXPENSE_HEADER]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TRN_EXPENSE_HEADER](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[DEBIT_TOTAL_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_TOTAL_AMOUNT] [numeric](15, 2) NULL,
	[DEBIT_400_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_400_AMOUNT] [numeric](15, 2) NULL,
	[DEBIT_401_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_401_AMOUNT] [numeric](15, 2) NULL,
	[DEBIT_402_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_402_AMOUNT] [numeric](15, 2) NULL,
	[DEBIT_406_AMOUNT] [numeric](15, 2) NULL,
	[CREDIT_406_AMOUNT] [numeric](15, 2) NULL,
	[BATCH_NO] [nvarchar](20) NULL,
 CONSTRAINT [PK_EXPENSE_HEADER] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[DOCTOR_CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TAX_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TAX_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](2) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_TAX_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[DOCTOR_CLASS_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_DOCTOR_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_GROUP_ITEM_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_GROUP_ITEM_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[GROUP_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](255) NOT NULL,
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_GROUP_ITEM_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[GROUP_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[ORDER_ITEM_CATEGORY_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ORDER_ITEM_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ORDER_ITEM_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[ACCOUNT_NO] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[ACCOUNT_NO_IPD] [nvarchar](20) NULL,
	[ACCOUNT_NO_OPD] [nvarchar](20) NULL,
 CONSTRAINT [PK_ORDER_ITEM_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAYMENT_MODE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PAYMENT_MODE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_PAYMENT_MODE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_PERIOD_EXPENSE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_PERIOD_EXPENSE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[EXPENSE_CODE] [nvarchar](20) NOT NULL,
	[TYPE_EXPENSE] [nvarchar](1) NOT NULL,
	[AMOUNT] [numeric](15, 2) NULL,
	[PAY_AMOUNT] [numeric](15, 2) NULL,
	[TOTAL_AMOUNT] [numeric](15, 2) NULL,
	[TAX_AMOUNT] [numeric](15, 2) NULL,
	[PAY_TAX_AMOUNT] [numeric](15, 2) NULL,
	[TOTAL_TAX_AMOUNT] [numeric](15, 2) NULL,
	[TAX_TYPE] [nvarchar](3) NULL,
	[START_TERM_MM] [nvarchar](8) NULL,
	[START_TERM_YYYY] [nvarchar](8) NULL,
	[END_TERM_MM] [nvarchar](8) NULL,
	[END_TERM_YYYY] [nvarchar](8) NULL,
	[NOTE] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](8) NULL,
	[UPDATE_TIME] [nvarchar](8) NULL,
 CONSTRAINT [PK_STP_PERIOD_EXPENSE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[EXPENSE_CODE] ASC,
	[TYPE_EXPENSE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRN_DAILY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TRN_DAILY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[INVOICE_NO] [nvarchar](30) NOT NULL,
	[INVOICE_DATE] [nvarchar](8) NOT NULL,
	[RECEIPT_NO] [nvarchar](20) NULL,
	[RECEIPT_DATE] [nvarchar](10) NULL,
	[TRANSACTION_DATE] [nvarchar](8) NOT NULL,
	[HN_NO] [nvarchar](20) NULL,
	[PATIENT_NAME] [nvarchar](255) NULL,
	[EPISODE_NO] [nvarchar](20) NULL,
	[NATIONALITY_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_NATIONALITY_CODE]  DEFAULT (''),
	[NATIONALITY_DESCRIPTION] [nvarchar](255) NULL CONSTRAINT [DF_TRN_DAILY_NATIONALITY_DESCRIPTION]  DEFAULT (''),
	[PAYOR_OFFICE_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_NAME] [nvarchar](255) NULL,
	[TRANSACTION_MODULE] [nvarchar](5) NULL,
	[TRANSACTION_TYPE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_CODE] [nvarchar](20) NULL,
	[PAYOR_OFFICE_CATEGORY_DESCRIPTION] [nvarchar](255) NULL,
	[IS_WRITE_OFF] [nvarchar](2) NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NULL,
	[FIRST_DEPARTMENT_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_FIRST_DEPARTMENT_CODE]  DEFAULT (''),
	[FIRST_LOCATION_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_FIRST_LOCATION_CODE]  DEFAULT (''),
	[PATIENT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[PATIENT_LOCATION_CODE] [nvarchar](20) NULL,
	[RECEIPT_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[RECEIPT_LOCATION_CODE] [nvarchar](20) NULL,
	[DOCTOR_DEPARTMENT_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_DESCRIPTION] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[VERIFY_DATE] [nvarchar](8) NULL,
	[VERIFY_TIME] [nvarchar](6) NULL,
	[DOCTOR_EXECUTE_CODE] [nvarchar](20) NULL,
	[EXECUTE_DATE] [nvarchar](8) NULL,
	[EXECUTE_TIME] [nvarchar](6) NULL,
	[DOCTOR_RESULT_CODE] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NULL,
	[RECEIPT_TYPE_CODE] [nvarchar](20) NULL,
	[AMOUNT_BEF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_OF_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_AFT_DISCOUNT] [numeric](15, 2) NULL,
	[AMOUNT_BEF_WRITE_OFF] [numeric](15, 2) NULL,
	[INV_IS_VOID] [nvarchar](2) NULL,
	[REC_IS_VOID] [nvarchar](2) NULL,
	[UPDATE_DATE] [nvarchar](8) NULL,
	[UPDATE_TIME] [nvarchar](6) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[BATCH_NO] [nvarchar](6) NULL,
	[YYYY] [nvarchar](8) NULL,
	[MM] [nvarchar](8) NULL,
	[DD] [nvarchar](8) NULL,
	[NOR_ALLOCATE_AMT] [numeric](15, 2) NULL,
	[NOR_ALLOCATE_PCT] [numeric](15, 2) NULL,
	[DR_AMT] [numeric](15, 2) NULL,
	[OLD_DR_AMT] [numeric](15, 2) NULL,
	[DR_TAX_400] [numeric](15, 2) NULL,
	[DR_TAX_401] [numeric](15, 2) NULL,
	[DR_TAX_402] [numeric](15, 2) NULL,
	[DR_TAX_406] [numeric](15, 2) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
	[DR_PREMIUM] [numeric](15, 2) NULL,
	[GUARANTEE_PAID_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_GUARANTEE_PAID_AMT]  DEFAULT ((0)),
	[GUARANTEE_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_GUARANTEE_AMT]  DEFAULT ((0)),
	[GUARANTEE_CODE] [nvarchar](20) NULL,
	[GUARANTEE_DR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_TYPE] [nvarchar](4) NULL,
	[GUARANTEE_DATE_TIME] [nvarchar](20) NULL,
	[GUARANTEE_TERM_MM] [nvarchar](2) NULL,
	[GUARANTEE_TERM_YYYY] [nvarchar](4) NULL,
	[GUARANTEE_NOTE] [nvarchar](50) NULL CONSTRAINT [DF_TRN_DAILY_GUARANTEE_NOTE]  DEFAULT (''),
	[IS_GUARANTEE] [nvarchar](2) NULL CONSTRAINT [DF_TRN_DAILY_IS_GUARANTEE]  DEFAULT ('N'),
	[HP_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[COMPUTE_DAILY_DATE] [nvarchar](8) NULL,
	[COMPUTE_DAILY_TIME] [nvarchar](8) NULL,
	[COMPUTE_DAILY_USER_ID] [nvarchar](20) NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NULL,
	[EXCLUDE_TREATMENT] [nvarchar](20) NULL,
	[PREMIUM_CHARGE_PCT] [nvarchar](20) NULL,
	[PREMIUM_REC_AMT] [numeric](15, 2) NULL,
	[ACTIVE] [nvarchar](2) NULL,
	[INVOICE_TYPE] [nvarchar](20) NOT NULL CONSTRAINT [DF_TRN_DAILY_INVOICE_TYPE]  DEFAULT ('EXECUTE'),
	[TOTAL_BILL_AMOUNT] [numeric](15, 2) NULL,
	[TOTAL_DR_REC_AMOUNT] [numeric](15, 2) NULL,
	[OLD_AMOUNT] [numeric](15, 2) NULL,
	[PAY_BY_CASH] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_PAY_BY_CASH]  DEFAULT ('N'),
	[PAY_BY_AR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_PAY_BY_AR]  DEFAULT ('N'),
	[PAY_BY_DOCTOR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_PAY_BY_DOCTOR]  DEFAULT ('N'),
	[PAY_BY_PAYOR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_PAY_BY_PAYOR]  DEFAULT ('N'),
	[PAY_BY_CASH_AR] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_PAY_BY_CASH_AR]  DEFAULT ('N'),
	[IS_PAID] [nvarchar](1) NOT NULL CONSTRAINT [DF_TRN_DAILY_IS_PAID]  DEFAULT ('Y'),
	[ORDER_ITEM_ACTIVE] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_ORDER_ITEM_ACTIVE]  DEFAULT ('0'),
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NULL,
	[WRITE_OFF_BILL_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_WRITE_OFF_BILL_AMT]  DEFAULT ((0)),
	[WRITE_OFF_RECEIPT_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_WRITE_OFF_RECEIPT_AMT]  DEFAULT ((0)),
	[OLD_DR_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_OLD_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[DR_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[DR_PREMIUM_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_DR_PREMIUM_BEF_WRITE_OFF]  DEFAULT ((0)),
	[HP_AMT_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_HP_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[HP_PREMIUM_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_HP_PREMIUM_WRITE_OFF]  DEFAULT ((0)),
	[OLD_TAX_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_OLD_TAX_AMT]  DEFAULT ((0)),
	[DR_TAX_406_BEF_WRITE_OFF] [numeric](15, 2) NULL CONSTRAINT [DF__TRN_DAILY__DR_TA__64B7E415]  DEFAULT ((0)),
	[TAX_FROM_ALLOCATE] [nvarchar](1) NULL CONSTRAINT [DF__TRN_DAILY__TAX_F__1431ED0D]  DEFAULT (''),
	[IS_GUARANTEE_FROM_ALLOC] [nvarchar](1) NULL CONSTRAINT [DF_TRN_DAILY_IS_GUARANTEE_FROM_ALLOC]  DEFAULT (''),
	[PRIVATE_DOCTOR_CODE] [nvarchar](20) NULL CONSTRAINT [DF_TRN_DAILY_PRIVATE_DOCTOR_CODE]  DEFAULT (''),
	[TAX_RATE] [numeric](5, 2) NULL CONSTRAINT [DF_TRN_DAILY_TAX_RATE]  DEFAULT ((1)),
	[OLD_NOR_ALLOCATE_PCT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_OLD_NOR_ALLOCATE_PCT]  DEFAULT ((0)),
	[OLD_NOR_ALLOCATE_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_TRN_DAILY_OLD_NOR_ALLOCATE_AMT]  DEFAULT ((0)),
 CONSTRAINT [PK_TRN_DAILY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[INVOICE_NO] ASC,
	[INVOICE_DATE] ASC,
	[TRANSACTION_DATE] ASC,
	[LINE_NO] ASC,
	[INVOICE_TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'hospital code' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TRN_DAILY', @level2type=N'COLUMN', @level2name=N'HOSPITAL_CODE'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BANK_TMB_MEDIA_CLEARING]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BANK_TMB_MEDIA_CLEARING](
	[FILE_TYPE] [nvarchar](2) NULL,
	[RECORD_TYPE] [nvarchar](1) NULL,
	[BATCH_NUMBER] [nvarchar](6) NULL,
	[RECEIVING_BANK_CODE] [nvarchar](3) NULL,
	[RECEIVING_BRANCH_CODE] [nvarchar](4) NULL,
	[RECEIVING_ACCOUNT_NO] [nvarchar](11) NULL,
	[SENDING_BANK_CODE] [nvarchar](3) NULL,
	[SENDING_BRANCH_CODE] [nvarchar](4) NULL,
	[SENDING_ACCOUNT_NO] [nvarchar](11) NULL,
	[EFFECTIVE_DATE] [nvarchar](8) NULL,
	[SERVICE_TYPE] [nvarchar](2) NOT NULL CONSTRAINT [DF_BANK_TMB_MEDIA_CLEARING_SERVICE_TYPE]  DEFAULT (''),
	[CLEARING_HOUSE_CODE] [nvarchar](2) NULL,
	[TRANSFER_AMOUNT] [nvarchar](12) NOT NULL,
	[RECEIVER_INFORMATION] [nvarchar](300) NULL,
	[SENDER_INFORMATION] [nvarchar](300) NULL,
	[OTHER_INFORMATION] [nvarchar](300) NULL,
	[REFERENCE_RUNNING_NO] [nvarchar](6) NOT NULL,
	[SPACE] [nvarchar](16) NULL,
	[COMPANY_CODE] [nvarchar](4) NULL,
	[SEQUENCE_NUMBER] [nvarchar](5) NOT NULL,
	[USER_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[BATCH_NO] [nvarchar](10) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_BANK_TMB_MEDIA_CLEARING] PRIMARY KEY CLUSTERED 
(
	[SERVICE_TYPE] ASC,
	[REFERENCE_RUNNING_NO] ASC,
	[SEQUENCE_NUMBER] ASC,
	[BATCH_NO] ASC,
	[HOSPITAL_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAYOR_OFFICE_SC]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PAYOR_OFFICE_SC](
	[PAYOR_OFFICE_CODE_SC] [nvarchar](20) NULL CONSTRAINT [DF_PAYOR_OFFICE_SC_PAYOR_OFFICE_CODE_SC]  DEFAULT (''),
	[HOSPITAL_CODE] [nvarchar](20) NULL CONSTRAINT [DF_PAYOR_OFFICE_SC_HOSPITAL_CODE]  DEFAULT ('')
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_ITEM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_ITEM](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[IS_PRIVATE] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_IS_PRIVATE]  DEFAULT ('N'),
	[TAX_RATE] [numeric](15, 2) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_TAX_RATE]  DEFAULT ((1)),
	[TAX_TYPE_CODE] [nvarchar](20) NOT NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_IS_ALLOC_FULL_TAX]  DEFAULT (''),
	[GUARANTEE_SOURCE] [nvarchar](10) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_GUARANTEE_SOURCE]  DEFAULT ('NONE'),
	[PRICE] [numeric](15, 2) NOT NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NOT NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NOT NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NOT NULL,
	[REMARK] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_METHOD_ALLOC_ITEM] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[ORDER_ITEM_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRN_ERROR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TRN_ERROR](
	[HOSPITAL_CODE] [nvarchar](5) NULL,
	[PROCESS_NAME] [nvarchar](50) NULL,
	[ERROR_TYPE] [nvarchar](5) NULL,
	[ERROR_DATE] [nvarchar](50) NULL,
	[ERROR_TIME] [nvarchar](50) NULL,
	[REMARK] [nvarchar](4000) NULL,
	[SYSTEM_MESSAGE] [nvarchar](4000) NULL CONSTRAINT [DF_TRN_ERROR_SYSTEM_MESSAGE]  DEFAULT (''),
	[SQLCOMMAND] [nvarchar](4000) NULL CONSTRAINT [DF_TRN_ERROR_SQLCOMMAND]  DEFAULT (''),
	[USER_ID] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[HOSPITAL_UNIT_CODE] [nvarchar](50) NULL,
	[DOCTOR_PROFILE_CODE] [nvarchar](50) NULL,
	[IS_GUARANTEE_PROFILE] [nvarchar](1) NULL CONSTRAINT [DF_DOCTOR_IS_GUARANTEE_PROFILE]  DEFAULT ('Y'),
	[GUARANTEE_DR_CODE] [nvarchar](50) NULL,
	[CODE] [nvarchar](50) NOT NULL,
	[NAME_THAI] [nvarchar](255) NULL,
	[NAME_ENG] [nvarchar](255) NULL,
	[LICENSE_ID] [nvarchar](50) NULL,
	[FROM_DATE] [nvarchar](50) NULL,
	[TO_DATE] [nvarchar](50) NULL,
	[BANK_ACCOUNT_NO] [nvarchar](50) NULL,
	[BANK_ACCOUNT_NAME] [nvarchar](255) NULL,
	[BANK_BRANCH_CODE] [nvarchar](50) NULL,
	[BANK_CODE] [nvarchar](50) NULL,
	[DOCTOR_TYPE_CODE] [nvarchar](50) NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](50) NULL,
	[PAY_TAX_402_BY] [nvarchar](2) NULL CONSTRAINT [DF_DOCTOR_PAY_TAX_402_BY]  DEFAULT ((0)),
	[PAYMENT_MODE_CODE] [nvarchar](50) NULL,
	[DEPARTMENT_CODE] [nvarchar](50) NULL,
	[TAX_ID] [nvarchar](50) NULL,
	[NOTE] [nvarchar](255) NULL,
	[ADDRESS1] [nvarchar](255) NULL,
	[ADDRESS2] [nvarchar](255) NULL,
	[ADDRESS3] [nvarchar](255) NULL,
	[ZIP] [nvarchar](50) NULL,
	[GUARANTEE_START_DATE] [nvarchar](10) NULL,
	[GUARANTEE_EXPIRE_DATE] [nvarchar](10) NULL,
	[GUARANTEE_SOURCE] [nvarchar](4) NULL CONSTRAINT [DF_DOCTOR_GUARANTEE_SOURCE]  DEFAULT ('NONE'),
	[OVER_GUARANTEE_PCT] [numeric](4, 0) NULL CONSTRAINT [DF_DOCTOR_OVER_GUARANTEE_PCT]  DEFAULT ((0)),
	[IN_GUARANTEE_PCT] [numeric](4, 0) NULL CONSTRAINT [DF_DOCTOR_IN_GUARANTEE_PCT]  DEFAULT ((100)),
	[IS_ADVANCE_PAYMENT] [nvarchar](50) NULL,
	[ACTIVE] [nvarchar](50) NULL,
	[UPDATE_DATE] [nvarchar](50) NULL,
	[UPDATE_TIME] [nvarchar](50) NULL,
	[USER_ID] [nvarchar](50) NULL,
	[SALARY] [numeric](15, 2) NULL CONSTRAINT [DF_DOCTOR_SALARY]  DEFAULT ((0)),
	[POSITION_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_DOCTOR_POSITION_AMT]  DEFAULT ((0)),
	[IS_HOLD] [nvarchar](1) NULL CONSTRAINT [DF_DOCTOR_IS_HOLD]  DEFAULT ('N'),
	[EMAIL] [varchar](250) NULL CONSTRAINT [DF__DOCTOR__EMAIL__7D8391DF]  DEFAULT (''),
	[GUARANTEE_DAY] [nvarchar](3) NULL CONSTRAINT [DF__DOCTOR__GUARANTE__0E7913B7]  DEFAULT (''),
	[HP_ABSORB_DOCTOR_CODE] [nvarchar](50) NULL CONSTRAINT [DF_DOCTOR_HP_ABSORB_DOCTOR_CODE]  DEFAULT (''),
	[SUB_SPECIALTY] [nvarchar](50) NULL,
	[DOCTOR_TAX_CODE] [nvarchar](50) DEFAULT ('') 
 CONSTRAINT [PK_DOCTOR_1] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_PRORATE_DOCTOR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_PRORATE_DOCTOR](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[GROUP_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](255) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[TYPE] [nvarchar](4) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](50) NULL,
	[UPDATE_TIME] [nvarchar](50) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_PRORATE_DOCTOR] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[GROUP_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TAX_91]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TAX_91](
	[YYYY] [nvarchar](4) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_STATUS] [nvarchar](1) NULL,
	[DOCTOR_TEL_HOME] [nvarchar](50) NULL,
	[DOCTOR_TEL_OFFICE] [nvarchar](50) NULL,
	[SPOUSE_BIRTHDAY] [nvarchar](10) NULL,
	[SPOUSE_NO] [nvarchar](13) NULL,
	[SPOUSE_TAX_NO] [nvarchar](50) NULL,
	[SPOUSE_NAME] [nvarchar](255) NULL,
	[SPOUSE_TYPE] [nvarchar](1) NULL,
	[SPOUSE_TYPE1] [nvarchar](1) NULL,
	[PHALANX_01] [nvarchar](1) NULL,
	[PHALANX_01_01] [nvarchar](3) NULL,
	[PHALANX_02] [nvarchar](1) NULL,
	[PHALANX_02_01] [nvarchar](3) NULL,
	[A01] [numeric](15, 2) NULL,
	[A02] [numeric](15, 2) NULL,
	[A03] [numeric](15, 2) NULL,
	[A04] [numeric](15, 2) NULL,
	[A05] [numeric](15, 2) NULL,
	[A06] [numeric](15, 2) NULL,
	[A07] [numeric](15, 2) NULL,
	[A08] [numeric](15, 2) NULL,
	[A09] [numeric](15, 2) NULL,
	[A10] [numeric](15, 2) NULL,
	[A11] [numeric](15, 2) NULL,
	[A12] [numeric](15, 2) NULL,
	[A13] [numeric](15, 2) NULL,
	[A14_01] [nvarchar](1) NULL,
	[A14_02] [numeric](5, 0) NULL,
	[A14_03] [numeric](15, 2) NULL,
	[A14_04] [nvarchar](1) NULL,
	[A15] [numeric](15, 2) NULL,
	[A16] [numeric](15, 2) NULL,
	[A17] [numeric](15, 2) NULL,
	[A18_01] [nvarchar](1) NULL,
	[A18_02] [numeric](15, 2) NULL,
	[A19] [numeric](15, 2) NULL,
	[A20_01] [nvarchar](1) NULL,
	[A20_02] [numeric](15, 2) NULL,
	[B01] [numeric](15, 2) NULL,
	[B02] [numeric](15, 2) NULL,
	[B03] [numeric](15, 2) NULL,
	[B04] [numeric](15, 2) NULL,
	[B05] [numeric](15, 2) NULL,
	[B06] [numeric](15, 2) NULL,
	[B07] [numeric](15, 2) NULL,
	[C01] [numeric](15, 2) NULL,
	[C02] [numeric](15, 2) NULL,
	[C03_01] [numeric](5, 0) NULL,
	[C03_02] [numeric](15, 2) NULL,
	[C03_03] [numeric](5, 0) NULL,
	[C03_04] [numeric](15, 2) NULL,
	[C04_01] [nvarchar](13) NULL,
	[C04_02] [numeric](15, 2) NULL,
	[C04_03] [nvarchar](13) NULL,
	[C04_04] [numeric](15, 2) NULL,
	[C04_05] [nvarchar](13) NULL,
	[C04_06] [numeric](15, 2) NULL,
	[C04_07] [nvarchar](13) NULL,
	[C04_08] [numeric](15, 2) NULL,
	[C05] [numeric](15, 2) NULL,
	[C06_01] [nvarchar](13) NULL,
	[C06_02] [nvarchar](13) NULL,
	[C06_03] [nvarchar](13) NULL,
	[C06_04] [nvarchar](13) NULL,
	[C06_05] [numeric](15, 2) NULL,
	[C07] [numeric](15, 2) NULL,
	[C08] [numeric](15, 2) NULL,
	[C09] [numeric](15, 2) NULL,
	[C10] [numeric](15, 2) NULL,
	[C11] [numeric](15, 2) NULL,
	[C12] [numeric](15, 2) NULL,
	[C13] [numeric](15, 2) NULL,
	[C14_01] [numeric](15, 2) NULL,
	[C14_02] [numeric](5, 0) NULL,
	[CREATE_ID] [nvarchar](20) NULL,
	[CREATE_DATE] [nvarchar](50) NULL,
	[CREATE_TIME] [nvarchar](50) NULL,
	[UPDATE_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
 CONSTRAINT [PK_TAX_91] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_TREATMENT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_TREATMENT](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_DOCTOR_TREATMENT] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TB_MAPPING_BANK_BRANCH]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TB_MAPPING_BANK_BRANCH](
	[BANK_CODE] [varchar](3) NOT NULL,
	[BANK_NAME] [varchar](250) NULL CONSTRAINT [DF_TB_MAPPING_BANK_BRANCH_BANK_NAME]  DEFAULT (''),
	[BANK_BRANCH_CODE] [varchar](4) NULL CONSTRAINT [DF_TB_MAPPING_BANK_BRANCH_BANK_BRANCH_CODE]  DEFAULT (''),
	[HOSPITAL_CODE] [varchar](5) NULL CONSTRAINT [DF_TB_MAPPING_BANK_BRANCH_HOSPITAL_CODE]  DEFAULT ('')
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_SUM_GUARANTEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_SUM_GUARANTEE](
	[HOSPITAL_CODE] [nvarchar](20) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_DR_CODE] [nvarchar](20) NULL,
	[GUARANTEE_CODE] [nvarchar](50) NULL,
	[GUARANTEE_LOCATION] [nvarchar](20) NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](2) NULL,
	[MM] [nvarchar](2) NULL,
	[YYYY] [nvarchar](4) NULL,
	[GUARANTEE_AMOUNT] [numeric](15, 2) NULL,
	[GUARANTEE_FIX_AMOUNT] [numeric](15, 2) NULL,
	[GUARANTEE_INCLUDE_AMOUNT] [numeric](15, 2) NULL,
	[GUARANTEE_EXCLUDE_AMOUNT] [numeric](15, 2) NULL,
	[DF406_CASH_AMOUNT] [numeric](15, 2) NULL,
	[DF406_CREDIT_AMOUNT] [numeric](15, 2) NULL,
	[DF402_CASH_AMOUNT] [numeric](15, 2) NULL,
	[DF402_CREDIT_AMOUNT] [numeric](15, 2) NULL,
	[DF400_CASH_AMOUNT] [numeric](15, 2) NULL,
	[DF400_CREDIT_AMOUNT] [numeric](15, 2) NULL,
	[DF406_HOLD_AMOUNT] [numeric](15, 2) NULL,
	[DF402_HOLD_AMOUNT] [numeric](15, 2) NULL,
	[DF400_HOLD_AMOUNT] [numeric](15, 2) NULL,
	[DF_CASH_AMOUNT] [numeric](15, 2) NULL,
	[DF_HOLD_AMOUNT] [numeric](15, 2) NULL,
	[DF406_ABSORB_AMOUNT] [numeric](15, 2) NULL,
	[DF402_ABSORB_AMOUNT] [numeric](15, 2) NULL,
	[DF400_ABSORB_AMOUNT] [numeric](15, 2) NULL,
	[DF_ABSORB_AMOUNT] [numeric](15, 2) NULL,
	[HP402_ABSORB_AMOUNTT] [numeric](15, 2) NULL,
	[OVER_GUARANTEE_AMOUNT] [numeric](15, 2) NULL,
	[SUM_DR_OVER_AMOUNT] [numeric](15, 2) NULL,
	[SUM_HP_OVER_AMOUNT] [numeric](15, 2) NULL,
	[GUARANTEE_PAID_AMOUNT] [numeric](15, 2) NULL,
	[SUM_TAX_406] [numeric](15, 2) NULL,
	[SUM_TAX_402] [numeric](15, 2) NULL,
	[SUM_TAX_400] [numeric](15, 2) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ORDER_ITEM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ORDER_ITEM](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[HANDICRAFT] [nvarchar](1) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NULL,
	[ACCOUNT_CODE] [nvarchar](20) NULL,
	[IS_COMPUTE] [nvarchar](1) NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL,
	[IS_GUARANTEE] [nvarchar](1) NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_ORDER_ITEM] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAYOR_OFFICE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PAYOR_OFFICE](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[PAYOR_OFFICE_CATEGORY_CODE] [nvarchar](20) NULL,
	[NAME_THAI] [nvarchar](255) NULL,
	[NAME_ENG] [nvarchar](255) NULL,
	[ADDRESS] [nvarchar](255) NULL,
	[TEL] [nvarchar](20) NULL,
	[IS_ADVANCE_PAYMENT] [nvarchar](1) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_PAYOR_OFFICE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACCOUNTNO] [nvarchar](10) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CLASS_CODE] [nvarchar](20) NOT NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_DOCTOR_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CLASS_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DEPARTMENT](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[CODE] [nvarchar](20) NOT NULL,
	[DEFAULT_LOCATION_CODE] [nvarchar](20) NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[HOSPITAL_UNIT_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_DEPARTMENT_GROUP] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMAIL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EMAIL](
	[CODE] [nvarchar](10) NOT NULL,
	[EMAIL] [nvarchar](50) NOT NULL,
	[TYPE] [nvarchar](10) NOT NULL,
	[NAME] [nvarchar](255) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_ITEM_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_ITEM_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[IS_PRIVATE] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_CATEGORY_IS_PRIVATE]  DEFAULT ('N'),
	[TAX_RATE] [numeric](5, 2) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_CATEGORY_TAX_RATE]  DEFAULT ((1)),
	[TAX_TYPE_CODE] [nvarchar](5) NOT NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_CATEGORY_IS_ALLOC_FULL_TAX]  DEFAULT (''),
	[GUARANTEE_SOURCE] [nvarchar](5) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_ITEM_CATEGORY_GUARANTEE_SOURCE]  DEFAULT ('NONE'),
	[PRICE] [numeric](15, 2) NOT NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NOT NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NOT NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NOT NULL,
	[REMARK] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NOT NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_METHOD_ALLOC_ITEM_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[ORDER_ITEM_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAYMENT_MONTHLY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PAYMENT_MONTHLY](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
	[SUM_AMT] [numeric](15, 2) NULL,
	[SUM_DISC_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_AMT] [numeric](15, 2) NULL,
	[SUM_PATIENT_PAID_AMT] [numeric](15, 2) NULL,
	[DR_SUM_AMT] [numeric](15, 2) NULL,
	[DR_DISC_AMT] [numeric](15, 2) NULL,
	[DR_PREMIUM_AMT] [numeric](15, 2) NULL,
	[DR_CREDIT_AMT] [numeric](15, 2) NULL,
	[DR_DEBIT_AMT] [numeric](15, 2) NULL,
	[DR_NET_PAID_AMT] [numeric](15, 2) NULL,
	[DR_NET_TAX_406_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_DR_NET_TAX_AMT]  DEFAULT ((0)),
	[SUM_PAY_BY_CASH] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_SUM_PAY_BY_CASH]  DEFAULT ((0)),
	[SUM_PAY_BY_AR] [numeric](15, 2) NULL,
	[DR_TAX_400] [numeric](15, 2) NULL,
	[DR_TAX_401] [numeric](15, 2) NULL,
	[DR_TAX_402] [numeric](15, 2) NULL,
	[DR_TAX_406] [numeric](15, 2) NULL,
	[HP_SUM_AMT] [numeric](15, 2) NULL,
	[HP_DISC_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM_AMT] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[GUARUNTEE_AMT] [numeric](15, 2) NULL,
	[GUARUNTEE_TAX] [numeric](15, 2) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[STATUS_MODIFY] [nvarchar](10) NULL,
	[BATCH_NO] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NULL,
	[REMARK] [nvarchar](500) NULL,
	[PAYMENT_MODE_CODE] [nvarchar](20) NULL,
	[REF_PAID_NO] [nvarchar](50) NULL,
	[PAY_DATE] [nvarchar](10) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_PAY_DATE]  DEFAULT (''),
	[RECEIPT_CHEQUE_DATE] [nvarchar](10) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CHEQUE_DATE]  DEFAULT (''),
	[PAYMENT_TERM_DATE] [nvarchar](10) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_PAYMENT_TERM_DATE]  DEFAULT (''),
	[DR_STEP_AMT] [numeric](15, 2) NULL,
	[HP_STEP_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_REC_AMT] [numeric](15, 2) NULL,
	[EDIT_DATE_OF_PAY_DATE] [nvarchar](10) NULL,
	[CASH_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CASH_AMT]  DEFAULT ((0)),
	[CASH_400] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CASH_400]  DEFAULT ((0)),
	[CASH_401] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CASH_401]  DEFAULT ((0)),
	[CASH_402] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CASH_402]  DEFAULT ((0)),
	[CASH_406] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_CASH_406]  DEFAULT ((0)),
	[AR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_AR_AMT]  DEFAULT ((0)),
	[AR_400] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_AR_400]  DEFAULT ((0)),
	[AR_401] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_AR_401]  DEFAULT ((0)),
	[AR_402] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_AR_402]  DEFAULT ((0)),
	[AR_406] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_AR_406]  DEFAULT ((0)),
	[EXDR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EXDR_AMT]  DEFAULT ((0)),
	[EXCR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EXCR_AMT]  DEFAULT ((0)),
	[EX_400] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EX_400]  DEFAULT ((0)),
	[EX_401] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EX_401]  DEFAULT ((0)),
	[EX_402] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EX_402]  DEFAULT ((0)),
	[EX_406] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_EX_406]  DEFAULT ((0)),
	[GDR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_GDR_AMT]  DEFAULT ((0)),
	[GDR_402] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_GDR_402]  DEFAULT ((0)),
	[GDR_406] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_GDR_406]  DEFAULT ((0)),
	[GCR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_GCR_AMT]  DEFAULT ((0)),
	[ABCR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_ABCR_AMT]  DEFAULT ((0)),
	[ABCR_402] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_ABCR_402]  DEFAULT ((0)),
	[ABCR_406] [numeric](15, 2) NULL CONSTRAINT [DF_PAYMENT_MONTHLY_ABCR_406]  DEFAULT ((0)),
	[TAX_406_DATE] [nvarchar](10) NOT NULL CONSTRAINT [DF_PAYMENT_MONTHLY_TAX_406_DATE]  DEFAULT (''),
	[IS_HOLD] [nvarchar](1) NOT NULL CONSTRAINT [DF_PAYMENT_MONTHLY_IS_HOLD]  DEFAULT (''),
	[SALARY] [numeric](15, 2) NOT NULL CONSTRAINT [DF_PAYMENT_MONTHLY_SALARY]  DEFAULT ((0)),
	[POSITION_AMT] [numeric](15, 2) NOT NULL CONSTRAINT [DF_PAYMENT_MONTHLY_POSITION_AMT]  DEFAULT ((0)),
	[POSITION_TAX_AMT] [numeric](15, 2) NULL,
	[PAYMENT_TYPE] [nvarchar](2) NOT NULL CONSTRAINT [DF_PAYMENT_MONTHLY_PAYMENT_TYPE]  DEFAULT (''),
 CONSTRAINT [PK_PAYMENT_MONTHLY_1] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[PAYMENT_TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_MONTHLY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_MONTHLY](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
	[SUM_AMT] [numeric](15, 2) NULL,
	[SUM_DISC_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_AMT] [numeric](15, 2) NULL,
	[SUM_PATIENT_PAID_AMT] [numeric](15, 2) NULL,
	[DR_SUM_AMT] [numeric](15, 2) NULL,
	[DR_DISC_AMT] [numeric](15, 2) NULL,
	[DR_PREMIUM_AMT] [numeric](15, 2) NULL,
	[DR_CREDIT_AMT] [numeric](15, 2) NULL,
	[DR_DEBIT_AMT] [numeric](15, 2) NULL,
	[DR_NET_PAID_AMT] [numeric](15, 2) NULL,
	[SUM_PAY_BY_CASH] [numeric](15, 2) NULL,
	[SUM_PAY_BY_AR] [numeric](15, 2) NULL,
	[DR_TAX_400] [numeric](15, 2) NULL,
	[DR_TAX_401] [numeric](15, 2) NULL,
	[DR_TAX_402] [numeric](15, 2) NULL,
	[DR_TAX_406] [numeric](15, 2) NULL,
	[HP_SUM_AMT] [numeric](15, 2) NULL,
	[HP_DISC_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM_AMT] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[GUARUNTEE_AMT] [numeric](15, 2) NULL,
	[GUARUNTEE_TAX] [numeric](15, 2) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[STATUS_MODIFY] [nvarchar](10) NULL,
	[BATCH_NO] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[REMARK] [nvarchar](255) NULL,
	[PAYMENT_MODE_CODE] [nvarchar](20) NULL,
	[REF_PAID_NO] [nvarchar](50) NULL,
	[PAY_DATE] [nvarchar](10) NULL,
	[PAYMENT_TERM_DATE] [nvarchar](10) NULL,
	[DR_STEP_AMT] [numeric](15, 2) NULL,
	[HP_STEP_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_REC_AMT] [numeric](15, 2) NULL,
	[EDIT_DATE_OF_PAY_DATE] [nvarchar](10) NULL,
	[CASH_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_CASH_AMT]  DEFAULT ((0)),
	[CASH_400] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_CASH_400]  DEFAULT ((0)),
	[CASH_401] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_CASH_401]  DEFAULT ((0)),
	[CASH_402] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_CASH_402]  DEFAULT ((0)),
	[CASH_406] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_CASH_406]  DEFAULT ((0)),
	[AR_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_AR_AMT]  DEFAULT ((0)),
	[AR_400] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_AR_400]  DEFAULT ((0)),
	[AR_401] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_AR_401]  DEFAULT ((0)),
	[AR_402] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_AR_402]  DEFAULT ((0)),
	[AR_406] [numeric](15, 2) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_AR_406]  DEFAULT ((0)),
	[IS_HOLD] [nvarchar](1) NULL CONSTRAINT [DF_SUMMARY_MONTHLY_IS_HOLD]  DEFAULT (''),
 CONSTRAINT [PK_SUMMARY_MONTHLY] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RECEIPT_MODE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RECEIPT_MODE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[TYPE] [nvarchar](10) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_RECEIPT_MODE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STATUS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STATUS](
	[CODE] [nvarchar](10) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STATUS] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STEP_SHARING]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STEP_SHARING](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[BEGIN_AMOUNT] [numeric](15, 2) NULL,
	[END_AMOUNT] [numeric](15, 2) NULL,
	[DR_PERCENT] [numeric](15, 2) NULL,
	[HP_PERCENT] [numeric](15, 2) NULL,
	[SORDER] [numeric](5, 0) NULL,
	[DOCTOR_CLASS_CODE] [nvarchar](20) NOT NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STEP_SHARING] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[DOCTOR_CLASS_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_GROUP]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[USER_GROUP](
	[USER_GROUP] [numeric](2, 0) NOT NULL,
	[ACTION_TYPE] [nvarchar](50) NULL,
	[PERMISSION] [nvarchar](300) NULL CONSTRAINT [DF_USER_GROUP_PERMISSION_1]  DEFAULT ((0)),
 CONSTRAINT [PK_TB_USER_GROUP] PRIMARY KEY CLUSTERED 
(
	[USER_GROUP] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RECEIPT_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RECEIPT_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[IS_CHARGE] [nvarchar](1) NULL,
	[PERCENT_OF_CHARGE] [numeric](15, 2) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[BANK_CODE] [nvarchar](20) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[RECEIPT_MODE_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_RECEIPT_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DOCTOR_TAX_TYPE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DOCTOR_TAX_TYPE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
 CONSTRAINT [PK_DOCTOR_TAX_TYPE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_ERP_GL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_ERP_GL](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[SEQ_NO] [numeric](15, 0) NOT NULL,
	[ACCOUNTING_DT] [nvarchar](10) NOT NULL,
	[ACCOUNTING_TIME] [nvarchar](50) NOT NULL,
	[TYPE] [nvarchar](10) NOT NULL,
	[YYYY] [nvarchar](50) NULL,
	[MM] [nvarchar](50) NULL,
	[APPL_JRNL_ID] [nvarchar](10) NULL,
	[ACCOUNT] [nvarchar](6) NULL,
	[DEPT_ID] [nvarchar](10) NULL,
	[PRODUCT] [nvarchar](6) NULL,
	[PROJECT_ID] [nvarchar](15) NULL,
	[CURRENCY_CD] [nvarchar](3) NULL,
	[STATISTICS_CODE] [nvarchar](3) NULL,
	[STATISTICS_AMOUNT] [numeric](15, 2) NULL,
	[MONETARY_AMOUNT] [numeric](15, 2) NULL,
	[OPERATING_UNIT] [nvarchar](8) NULL,
	[CLASS_FLD] [nvarchar](5) NULL,
	[CHARTFIELD1] [nvarchar](10) NULL,
	[CHARTFIELD2] [nvarchar](10) NULL,
	[CHARTFIELD3] [nvarchar](10) NULL,
	[FUND_CODE] [nvarchar](5) NULL,
	[PROGRAM_CODE] [nvarchar](5) NULL,
	[AMOUNT_SIGN] [nvarchar](1) NULL,
 CONSTRAINT [PK_INT_ERP_GL] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[SEQ_NO] ASC,
	[ACCOUNTING_DT] ASC,
	[ACCOUNTING_TIME] ASC,
	[TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[USERS](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[LOGIN_NAME] [nvarchar](50) NOT NULL,
	[PASSWORD] [nvarchar](32) NOT NULL,
	[NAME] [nvarchar](255) NULL,
	[USER_GROUP_CODE] [numeric](2, 0) NULL,
	[ACTIVE] [nvarchar](50) NULL,
	[LOGIN_DATE] [nvarchar](50) NULL,
	[LOGIN_TIME] [nvarchar](50) NULL,
	[IPADDRESS] [nvarchar](50) NULL,
	[EMAIL] [nvarchar](100) NULL,
	[LANG_CODE] [nchar](1) NULL,
 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[LOGIN_NAME] ASC,
	[PASSWORD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_TAX_REDUCE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_TAX_REDUCE](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[YYYY] [nvarchar](8) NOT NULL,
	[MM] [nvarchar](8) NOT NULL,
	[DOCTOR_STATUS] [nvarchar](1) NOT NULL,
	[SPOUSE_BIRTHDAY] [nvarchar](10) NULL,
	[SPOUSE_NO] [nvarchar](13) NULL,
	[SPOUSE_TAX_NO] [nvarchar](50) NULL,
	[SPOUSE_NAME] [nvarchar](255) NULL,
	[SPOUSE_TYPE] [nvarchar](1) NULL,
	[SPOUSE_TYPE1] [nvarchar](1) NULL,
	[A08] [numeric](15, 2) NULL,
	[A081] [numeric](15, 2) NULL,
	[A10] [numeric](15, 2) NULL,
	[A101] [numeric](15, 2) NULL,
	[B01] [numeric](15, 2) NULL,
	[B011] [numeric](15, 2) NULL,
	[B02] [numeric](15, 2) NULL,
	[B021] [numeric](15, 2) NULL,
	[B03] [numeric](15, 2) NULL,
	[B031] [numeric](15, 2) NULL,
	[B04] [numeric](15, 2) NULL,
	[B05] [numeric](15, 2) NULL,
	[B06] [numeric](15, 2) NULL,
	[B061] [numeric](15, 2) NULL,
	[B07] [numeric](15, 2) NULL,
	[B071] [numeric](15, 2) NULL,
	[C01] [numeric](15, 2) NULL,
	[C02] [numeric](15, 2) NULL,
	[C03_01] [numeric](5, 0) NULL,
	[C03_02] [numeric](15, 2) NULL,
	[C03_03] [numeric](5, 0) NULL,
	[C03_04] [numeric](15, 2) NULL,
	[C04_01] [nvarchar](13) NULL,
	[C04_02] [numeric](15, 2) NULL,
	[C04_03] [nvarchar](13) NULL,
	[C04_04] [numeric](15, 2) NULL,
	[C04_05] [nvarchar](13) NULL,
	[C04_06] [numeric](15, 2) NULL,
	[C04_07] [nvarchar](13) NULL,
	[C04_08] [numeric](15, 2) NULL,
	[C05] [numeric](15, 2) NULL,
	[C051] [numeric](15, 2) NULL,
	[C06_01] [nvarchar](13) NULL,
	[C06_02] [nvarchar](13) NULL,
	[C06_03] [nvarchar](13) NULL,
	[C06_04] [nvarchar](13) NULL,
	[C06_05] [numeric](15, 2) NULL,
	[C06_051] [numeric](15, 2) NULL,
	[STATUS] [nvarchar](1) NULL CONSTRAINT [DF_STP_TAX_REDUCE_STATUS]  DEFAULT ((1)),
	[CREATE_ID] [nvarchar](20) NULL,
	[CREATE_DATE] [nvarchar](50) NULL,
	[CREATE_TIME] [nvarchar](50) NULL,
	[UPDATE_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
 CONSTRAINT [PK_STP_TAX_REDUCE] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC,
	[DOCTOR_STATUS] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_DR_ITEM_CATEGORY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_DR_ITEM_CATEGORY](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[PRICE] [numeric](15, 2) NOT NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NOT NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NOT NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NOT NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NOT NULL,
	[IS_PRIVATE] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_DR_ITEM_CATEGORY_IS_PRIVATE]  DEFAULT ('N'),
	[GUARANTEE_SOURCE] [nvarchar](10) NULL,
	[REMARK] [nvarchar](255) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_DR_ITEM_CATEGORY_REMARK]  DEFAULT (''),
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](20) NULL,
	[UPDATE_TIME] [nvarchar](20) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_DR_ITEM_CATEGORY_IS_ALLOC_FULL_TAX]  DEFAULT (''),
	[TAX_RATE] [numeric](15, 2) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_DR_ITEM_CATEGORY_TAX_RATE]  DEFAULT ((1)),
 CONSTRAINT [PK_STP_METHOD_ALLOC_DR_ITEM_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[ORDER_ITEM_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[PRICE] ASC,
	[TAX_TYPE_CODE] ASC,
	[EXCLUDE_TREATMENT] ASC,
	[NORMAL_ALLOCATE_PCT] ASC,
	[NORMAL_ALLOCATE_AMT] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_HIS_DOCTOR_CO]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_HIS_DOCTOR_CO](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[BILL_NO] [nvarchar](20) NOT NULL,
	[TRANSACTION_DATE] [nvarchar](10) NOT NULL,
	[MEDICAL_FEE] [numeric](15, 2) NULL,
	[CO_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_INT_HIS_DOCTOR_CO] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[BILL_NO] ASC,
	[TRANSACTION_DATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_GL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TEMP_GL](
	[ADMISSION_TYPE_CODE] [varchar](50) NULL,
	[PATIENT_DEPARTMENT_CODE] [varchar](50) NULL,
	[AMOUNT_AFT_DISCOUNT] [numeric](15, 2) NULL,
	[ACCOUNT_CODE] [varchar](50) NULL,
	[RECEIPT_LOCATION] [varchar](50) NULL,
	[PATIENT_LOCATION] [varchar](50) NULL,
	[NATIONALITY_CODE] [varchar](50) NULL,
	[AMOUNT_SIGN] [varchar](1) NULL,
	[HOSPITAL_CODE] [varchar](10) NULL,
	[YYYY] [varchar](50) NULL,
	[MM] [varchar](50) NULL,
	[LINE_NO] [varchar](50) NULL CONSTRAINT [DF__TEMP_GL__LINE_NO__79B300FB]  DEFAULT (' '),
	[DOCTOR_CODE] [varchar](20) NULL CONSTRAINT [DF__TEMP_GL__DOCTOR___7AA72534]  DEFAULT (' '),
	[PROCESS] [nvarchar](2) NULL CONSTRAINT [DF__TEMP_GL__PROCESS__002AF460]  DEFAULT ('')
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_MASTER_TAX_REDUCE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_MASTER_TAX_REDUCE](
	[CODE] [int] IDENTITY(1,1) NOT NULL,
	[MASTER_REDUCE_NAME] [nvarchar](255) NOT NULL,
	[MASTER_REDUCE_MAX_AMOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_MASTER_TAX_REDUCE_MASTER_REDUCE_MAX_AMOUNT]  DEFAULT ((0)),
	[MASTER_REDUCE_MAX_PERCENT] [numeric](15, 2) NULL CONSTRAINT [DF_STP_MASTER_TAX_REDUCE_MASTER_REDUCE_MAX_PERCENT]  DEFAULT ((0)),
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[STATUS] [int] NULL,
	[CREATE_ID] [nvarchar](20) NULL,
	[CREATE_DATE] [nvarchar](50) NULL,
	[CREATE_TIME] [nvarchar](50) NULL,
	[UPDATE_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[YYYY] [nvarchar](4) NOT NULL CONSTRAINT [DF__STP_MASTER__YYYY__19EAC663]  DEFAULT (''),
 CONSTRAINT [PK_STP_MASTER_TAX_REDUCE] PRIMARY KEY CLUSTERED 
(
	[MASTER_REDUCE_NAME] ASC,
	[HOSPITAL_CODE] ASC,
	[YYYY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_MASTER]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_MASTER](
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[IS_PRIVATE] [nvarchar](1) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_MASTER_IS_PRIVATE]  DEFAULT ('N'),
	[PRICE] [numeric](15, 2) NOT NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NOT NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NOT NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NOT NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_MASTER_IS_ALLOC_FULL_TAX]  DEFAULT (''),
	[TAX_RATE] [numeric](15, 2) NOT NULL CONSTRAINT [DF_STP_METHOD_ALLOC_MASTER_TAX_RATE]  DEFAULT ((1)),
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[GUARANTEE_SOURCE] [nvarchar](10) NULL CONSTRAINT [DF_STP_METHOD_ALLOC_MASTER_GUARANTEE_SOURCE]  DEFAULT ('NONE'),
	[REMARK] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_METHOD_ALLOC_MASTER] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_MENU_MATCH]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_MENU_MATCH](
	[USER_GROUP_CODE] [nvarchar](50) NULL,
	[MENU_CODE] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_PR_DOCTOR_INCOME]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_PR_DOCTOR_INCOME](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[EMPLOYEE_ID] [nvarchar](20) NOT NULL,
	[ELEMENT_TYPE] [nvarchar](20) NULL,
	[DEPARTMENT_CODE] [nvarchar](20) NOT NULL,
	[PRODUCT] [nvarchar](1) NULL,
	[PROJECT] [nvarchar](1) NULL,
	[ACTIVIVY] [nvarchar](1) NULL,
	[RATE] [nvarchar](1) NULL,
	[HOUR] [nvarchar](1) NULL,
	[AMOUNT] [numeric](15, 2) NULL,
	[UPLOAD] [nvarchar](1) NULL CONSTRAINT [DF_INT_PR_DOCTOR_INCOME_Upload]  DEFAULT ('U'),
	[TRANSACTION_DATE] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_INT_PR_DOCTOR_INCOME] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[EMPLOYEE_ID] ASC,
	[DEPARTMENT_CODE] ASC,
	[TRANSACTION_DATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Type of payment amount, either EDF or DFC ' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'ELEMENT_TYPE'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is Blank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'PRODUCT'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is Blank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'PROJECT'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is Blank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'ACTIVIVY'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is Blank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'RATE'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is Blank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'HOUR'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default is ''U''' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'INT_PR_DOCTOR_INCOME', @level2type=N'COLUMN', @level2name=N'UPLOAD'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_TAX_RATE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_TAX_RATE](
	[STEP_NO] [nvarchar](1) NULL,
	[AMOUNT_RATE] [numeric](7, 0) NULL,
	[PERCENT_RATE] [numeric](2, 2) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_MONTHLY_402]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_MONTHLY_402](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
	[SUM_AMT] [numeric](15, 2) NULL,
	[SUM_DISC_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_AMT] [numeric](15, 2) NULL,
	[SUM_PATIENT_PAID_AMT] [numeric](15, 2) NULL,
	[DR_SUM_AMT] [numeric](15, 2) NULL,
	[DR_DISC_AMT] [numeric](15, 2) NULL,
	[DR_PREMIUM_AMT] [numeric](15, 2) NULL,
	[DR_CREDIT_AMT] [numeric](15, 2) NULL,
	[DR_DEBIT_AMT] [numeric](15, 2) NULL,
	[DR_NET_PAID_AMT] [numeric](15, 2) NULL,
	[DR_TAX_40_2] [numeric](15, 2) NULL,
	[DR_TAX_40_6] [numeric](15, 2) NULL,
	[HP_SUM_AMT] [numeric](15, 2) NULL,
	[HP_DISC_AMT] [numeric](15, 2) NULL,
	[HP_PREMIUM_AMT] [numeric](15, 2) NULL,
	[HP_TAX] [numeric](15, 2) NULL,
	[GUARUNTEE_AMT] [numeric](15, 2) NULL,
	[GUARUNTEE_TAX] [numeric](15, 2) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[STATUS_MODIFY] [nvarchar](10) NULL,
	[BATCH_NO] [nvarchar](20) NULL,
	[OLD_DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[REMARK] [nvarchar](255) NULL,
	[PAYMENT_MODE_CODE] [nvarchar](20) NULL,
	[REF_PAID_NO] [nvarchar](50) NULL,
	[PAY_DATE] [nvarchar](10) NULL,
	[PAYMENT_TERM_DATE] [nvarchar](10) NULL,
	[DR_STEP_AMT] [numeric](15, 2) NULL,
	[HP_STEP_AMT] [numeric](15, 2) NULL,
	[SUM_PREMIUM_REC_AMT] [numeric](15, 2) NULL,
 CONSTRAINT [PK_SUMMARY_MONTHLY_402] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[OLD_DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_TAX_REDUCE_DETAIL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_TAX_REDUCE_DETAIL](
	[HOSPITAL_CODE] [nvarchar](50) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[YYYY] [nvarchar](8) NOT NULL,
	[MM] [nvarchar](8) NOT NULL,
	[MASTER_REDUCE_CODE] [int] NOT NULL,
	[AMOUNT_1] [numeric](15, 2) NULL,
	[AMOUNT_2] [numeric](15, 2) NULL,
	[CREATE_ID] [nvarchar](20) NULL,
	[CREATE_DATE] [nvarchar](50) NULL,
	[CREATE_TIME] [nvarchar](50) NULL,
	[UPDATE_ID] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
 CONSTRAINT [PK_STP_TAX_REDUCE_DETAIL] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC,
	[YYYY] ASC,
	[MM] ASC,
	[MASTER_REDUCE_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HOSPITAL_UNIT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HOSPITAL_UNIT](
	[CODE] [nvarchar](20) NULL,
	[DESCRIPTION] [nvarchar](250) NULL,
	[HOSPITAL_CODE] [nvarchar](20) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HOSPITAL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HOSPITAL](
	[CODE] [nvarchar](5) NOT NULL,
	[DESCRIPTION_THAI] [nvarchar](255) NULL,
	[DESCRIPTION_ENG] [nvarchar](255) NULL,
	[ADDRESS1] [nvarchar](255) NULL,
	[ADDRESS2] [nvarchar](255) NULL,
	[ADDRESS3] [nvarchar](255) NULL,
	[ZIP] [nvarchar](10) NULL,
	[TEL] [nvarchar](20) NULL,
	[FAX] [nvarchar](20) NULL,
	[EMAIL] [nvarchar](30) NULL,
	[TAXNO] [nvarchar](20) NULL,
	[CONTRACT_NAME_THAI] [nvarchar](255) NULL,
	[CONTRACT_NAME_ENG] [nvarchar](255) NULL,
	[CONTRACT_POSITION] [nvarchar](255) NULL,
	[CONTRACT_TEL] [nvarchar](20) NULL,
	[CONTRACT_EMAIL] [nvarchar](30) NULL,
	[COMPANY_NAME] [nvarchar](255) NULL CONSTRAINT [DF_HOSPITAL_COMPANY_NAME]  DEFAULT (''),
	[BANK_CODE] [nvarchar](20) NOT NULL,
	[BANK_BRANCH_CODE] [nvarchar](20) NOT NULL,
	[ACCOUNT_NO] [nvarchar](20) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[OF_ADDRESSNO] [nvarchar](50) NULL,
	[OF_MOO] [nvarchar](50) NULL,
	[OF_BUILDER] [nvarchar](50) NULL,
	[OF_BUILDER_NUMBER] [nvarchar](50) NULL,
	[OF_BUILDER_FLOOR] [nvarchar](50) NULL,
	[OF_VILLAGE] [nvarchar](50) NULL,
	[OF_SOI] [nvarchar](50) NULL,
	[OF_ROAD] [nvarchar](50) NULL,
	[OF_SUBDISTRICT] [nvarchar](50) NULL,
	[OF_DISTRICT] [nvarchar](50) NULL,
	[OF_PROVINCE] [nvarchar](50) NULL,
	[COMPANY_NAME1] [nvarchar](50) NULL,
	[TAX_END_MONTH_OF_YEAR] [nvarchar](2) NULL DEFAULT (''),
	[CITI_PARTY_NAME] [nvarchar](255) NULL DEFAULT (''),
	[CITI_PARTY_ID] [nvarchar](50) NULL DEFAULT (''),
	[CITI_PARTY_AC] [nvarchar](50) NULL DEFAULT (''),
	[GUARANTEE_INCLUDE_EXTRA] [nvarchar](1) NULL DEFAULT ('Y'),
	[GUARANTEE_DAY] [nvarchar](3) NULL DEFAULT (''),
	[GUARANTEE_ALL_ALLOC] [nvarchar](1) NULL DEFAULT (''),
	[GL_ACCOUNT_CODE] [nvarchar](6) NULL DEFAULT (''),
	[AC_ACCOUNT_CODE] [nvarchar](6) NULL DEFAULT (''),
 CONSTRAINT [PK_HOSPITAL] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LOG_DOCTOR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LOG_DOCTOR](
	[HOSPITAL_CODE] [nvarchar](50) NULL,
	[HOSPITAL_UNIT_CODE] [nvarchar](50) NULL,
	[DOCTOR_PROFILE_CODE] [nvarchar](50) NULL,
	[GUARANTEE_DR_CODE] [nvarchar](50) NULL,
	[CODE] [nvarchar](50) NULL,
	[NAME_THAI] [nvarchar](255) NULL,
	[NAME_ENG] [nvarchar](255) NULL,
	[LICENSE_ID] [nvarchar](50) NULL,
	[FROM_DATE] [nvarchar](50) NULL,
	[TO_DATE] [nvarchar](50) NULL,
	[BANK_ACCOUNT_NO] [nvarchar](50) NULL,
	[BANK_ACCOUNT_NAME] [nvarchar](255) NULL,
	[BANK_BRANCH_CODE] [nvarchar](50) NULL,
	[BANK_CODE] [nvarchar](50) NULL,
	[DOCTOR_TYPE_CODE] [nvarchar](50) NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](50) NULL,
	[PAY_TAX_402_BY] [nvarchar](2) NULL CONSTRAINT [DF_LOG_DOCTOR_PAY_TAX_402_BY]  DEFAULT ((0)),
	[PAYMENT_MODE_CODE] [nvarchar](50) NULL,
	[DEPARTMENT_CODE] [nvarchar](50) NULL,
	[TAX_ID] [nvarchar](50) NULL,
	[NOTE] [nvarchar](255) NULL,
	[ADDRESS1] [nvarchar](255) NULL,
	[ADDRESS2] [nvarchar](255) NULL,
	[ADDRESS3] [nvarchar](255) NULL,
	[ZIP] [nvarchar](50) NULL,
	[GUARANTEE_START_DATE] [nvarchar](10) NULL,
	[GUARANTEE_EXPIRE_DATE] [nvarchar](10) NULL,
	[GUARANTEE_SOURCE] [nvarchar](4) NULL,
	[OVER_GUARANTEE_PCT] [numeric](4, 0) NULL,
	[IS_ADVANCE_PAYMENT] [nvarchar](50) NULL,
	[ACTIVE] [nvarchar](50) NULL,
	[UPDATE_DATE] [nvarchar](50) NULL,
	[UPDATE_TIME] [nvarchar](50) NULL,
	[USER_ID] [nvarchar](50) NULL,
	[SALARY] [numeric](15, 2) NULL CONSTRAINT [DF_LOG_DOCTOR_SALARY]  DEFAULT ((0)),
	[POSITION_AMT] [numeric](15, 2) NULL CONSTRAINT [DF_LOG_DOCTOR_POSITION_AMT]  DEFAULT ((0)),
	[IS_HOLD] [nvarchar](1) NULL CONSTRAINT [DF_LOG_DOCTOR_IS_HOLD]  DEFAULT ('N'),
	[GUARANTEE_DAY] [nchar](3) NULL CONSTRAINT [DF_LOG_DOCTOR_GUARANTEE_DAY]  DEFAULT (''),
	[HP_ABSORB_DOCTOR_CODE] [nvarchar](50) NULL CONSTRAINT [DF_LOG_DOCTOR_HP_ABSORB_DOCTOR_CODE]  DEFAULT (''),
	[SUB_SPECIALTY] [nvarchar](50) NULL 
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MA_TRN_DAILY](
	[HOSPITAL_CODE] [nvarchar](20) COLLATE Thai_BIN NOT NULL,
	[INVOICE_NO] [nvarchar](30) COLLATE Thai_BIN NOT NULL,
	[INVOICE_DATE] [nvarchar](8) COLLATE Thai_BIN NOT NULL,
	[HN_NO] [nvarchar](20) COLLATE Thai_BIN NULL,
	[PATIENT_NAME] [nvarchar](255) COLLATE Thai_BIN NULL,
	[PAYOR_OFFICE_CODE] [nvarchar](20) COLLATE Thai_BIN NULL,
	[LINE_NO] [nvarchar](20) COLLATE Thai_BIN NOT NULL,
	[TRANSACTION_DATE] [nvarchar](8) COLLATE Thai_BIN NOT NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) COLLATE Thai_BIN NULL,
	[INVOICE_TYPE] [nvarchar](20) COLLATE Thai_BIN NOT NULL,
	[DOCTOR_CODE_OLD] [nvarchar](20) COLLATE Thai_BIN NULL,
	[DOCTOR_CODE_NEW] [nvarchar](20) COLLATE Thai_BIN NULL,
	[MA_TYPE] [nvarchar](3) COLLATE Thai_BIN NOT NULL,
	[DIS_TYPE] [nvarchar](1) COLLATE Thai_BIN NULL,
	[DIS_PERCENT] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_DIS_PERCENT]  DEFAULT ((0.00)),
	[AMOUNT_BEF_DISCOUNT] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_AMOUNT_BEF_DISCOUNT]  DEFAULT ((0.00)),
	[AMOUNT_OF_DISCOUNT_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_AMOUNT_OF_DISCOUNT_OLD]  DEFAULT ((0.00)),
	[AMOUNT_OF_DISCOUNT_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_AMOUNT_OF_DISCOUNT_NEW]  DEFAULT ((0.00)),
	[AMOUNT_AFT_DISCOUNT_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_AMOUNT_AFT_DISCOUNT_OLD]  DEFAULT ((0.00)),
	[AMOUNT_AFT_DISCOUNT_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_AMOUNT_AFT_DISCOUNT_NEW]  DEFAULT ((0.00)),
	[UPDATE_DATE] [nvarchar](8) COLLATE Thai_BIN NULL,
	[UPDATE_TIME] [nvarchar](8) COLLATE Thai_BIN NULL,
	[USER_ID] [nvarchar](20) COLLATE Thai_BIN NULL,
	[BATCH_NO] [nvarchar](6) COLLATE Thai_BIN NULL,
	[YYYY] [nvarchar](8) COLLATE Thai_BIN NULL,
	[MM] [nvarchar](8) COLLATE Thai_BIN NULL,
	[ACTIVE_OLD] [nvarchar](2) COLLATE Thai_BIN NULL,
	[ACTIVE_NEW] [nvarchar](2) COLLATE Thai_BIN NULL,
	[NOTE] [nvarchar](255) COLLATE Thai_BIN NULL,
	[AMOUNT_BEF_WRITE_OFF_OLD] [numeric](15, 2) NULL,
	[AMOUNT_BEF_WRITE_OFF_NEW] [numeric](15, 2) NULL,
	[DR_AMT_OLD] [numeric](15, 2) NULL,
	[DR_AMT_NEW] [numeric](15, 2) NULL,
	[OLD_DR_AMT_OLD] [numeric](15, 2) NULL,
	[OLD_DR_AMT_NEW] [numeric](15, 2) NULL,
	[DR_TAX_406_OLD] [numeric](15, 2) NULL,
	[DR_TAX_406_NEW] [numeric](15, 2) NULL,
	[OLD_AMOUNT_OLD] [numeric](15, 2) NULL,
	[OLD_AMOUNT_NEW] [numeric](15, 2) NULL,
	[OLD_DR_AMT_BEF_WRITE_OFF_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_OLD_DR_AMT_BEF_WRITE_OFF_NEW1]  DEFAULT ((0)),
	[OLD_DR_AMT_BEF_WRITE_OFF_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_OLD_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[DR_AMT_BEF_WRITE_OFF_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_DR_AMT_BEF_WRITE_OFF_NEW1]  DEFAULT ((0)),
	[DR_AMT_BEF_WRITE_OFF_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_DR_AMT_BEF_WRITE_OFF]  DEFAULT ((0)),
	[OLD_TAX_AMT_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_OLD_TAX_AMT_NEW1]  DEFAULT ((0)),
	[OLD_TAX_AMT_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_OLD_TAX_AMT]  DEFAULT ((0)),
	[DR_TAX_406_BEF_WRITE_OFF_OLD] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_DR_TAX_406_BEF_WRITE_OFF_NEW1]  DEFAULT ((0)),
	[DR_TAX_406_BEF_WRITE_OFF_NEW] [numeric](15, 2) NULL CONSTRAINT [DF_MA_TRN_DAILY_DR_TAX_406_BEF_WRITE_OFF]  DEFAULT ((0)),
 CONSTRAINT [PK_MA_TRN_DAILY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[INVOICE_NO] ASC,
	[INVOICE_DATE] ASC,
	[LINE_NO] ASC,
	[TRANSACTION_DATE] ASC,
	[INVOICE_TYPE] ASC,
	[MA_TYPE] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INT_HIS_VERIFY]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[INT_HIS_VERIFY](
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[BILL_NO] [nvarchar](20) NOT NULL,
	[LINE_NO] [nvarchar](20) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[VERIFIED_DATE] [nvarchar](10) NOT NULL,
	[VERIFIED_TIME] [nvarchar](10) NULL,
	[TRANSACTION_DATE] [nvarchar](10) NULL,
 CONSTRAINT [PK_INT_HIS_VERIFY] PRIMARY KEY CLUSTERED 
(
	[HOSPITAL_CODE] ASC,
	[BILL_NO] ASC,
	[LINE_NO] ASC,
	[VERIFIED_DATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STP_METHOD_ALLOC_PAYOR]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STP_METHOD_ALLOC_PAYOR](
	[LINE_NO] [nvarchar](100) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](20) NOT NULL,
	[ADMISSION_TYPE_CODE] [nvarchar](20) NOT NULL,
	[PAYOR_OFFICE_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_TREATMENT_CODE] [nvarchar](20) NOT NULL,
	[DOCTOR_CATEGORY_CODE] [nvarchar](20) NOT NULL,
	[IS_PRIVATE] [nvarchar](1) NOT NULL,
	[TAX_RATE] [numeric](7, 4) NULL,
	[TAX_TYPE_CODE] [nvarchar](20) NULL,
	[IS_ALLOC_FULL_TAX] [nvarchar](1) NULL,
	[GUARANTEE_SOURCE] [nvarchar](10) NULL,
	[PRICE] [numeric](15, 2) NULL,
	[NORMAL_ALLOCATE_PCT] [numeric](7, 4) NULL,
	[NORMAL_ALLOCATE_AMT] [numeric](15, 2) NULL,
	[EXCLUDE_TREATMENT] [nvarchar](1) NULL,
	[REMARK] [nvarchar](255) NULL,
	[DOCTOR_CODE] [nvarchar](20) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[ORDER_ITEM_CODE] [nvarchar](20) NULL,
	[ORDER_ITEM_CATEGORY_CODE] [nvarchar](20) NULL,
 CONSTRAINT [PK_STP_METHOD_ALLOC_PAYOR] PRIMARY KEY CLUSTERED 
(
	[LINE_NO] ASC,
	[HOSPITAL_CODE] ASC,
	[ADMISSION_TYPE_CODE] ASC,
	[PAYOR_OFFICE_CODE] ASC,
	[DOCTOR_TREATMENT_CODE] ASC,
	[DOCTOR_CATEGORY_CODE] ASC,
	[IS_PRIVATE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EXPENSE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EXPENSE](
	[CODE] [nvarchar](20) NOT NULL,
	[DESCRIPTION] [nvarchar](255) NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[SIGN] [numeric](4, 0) NULL,
	[ACTIVE] [nvarchar](1) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
	[ACCOUNT_CODE] [nvarchar](20) NULL,
	[ADJUST_TYPE] [nvarchar](2) NULL DEFAULT (''),
	[GL_INTERFACE] [nvarchar](1) NULL DEFAULT (''),
	[AC_INTERFACE] [nvarchar](1) NULL DEFAULT (''),
	[TAX_TYPE_CODE] [nvarchar](3) NULL DEFAULT (''),
 CONSTRAINT [PK_EXPENSE] PRIMARY KEY CLUSTERED 
(
	[CODE] ASC,
	[HOSPITAL_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SUMMARY_TAX_406]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SUMMARY_TAX_406](
	[YYYY] [nvarchar](4) NOT NULL,
	[MM] [nvarchar](2) NOT NULL,
	[HOSPITAL_CODE] [nvarchar](5) NOT NULL,
	[DOCTOR_CODE] [nvarchar](20) NOT NULL,
	[SUM_AMT] [numeric](15, 2) NOT NULL,
	[SUM_DR_AMT] [numeric](15, 2) NOT NULL,
	[SUM_HP_AMT] [numeric](15, 2) NOT NULL,
	[SUM_TAX_DR_AMT] [numeric](15, 2) NOT NULL,
	[SUM_TAX_HP_AMT] [numeric](15, 2) NOT NULL,
	[TEXT_SUM_DR_AMT] [nvarchar](255) NULL,
	[TEXT_SUM_TAX_DR_AMT] [nvarchar](255) NULL,
	[PRINT_DATE] [nvarchar](10) NULL,
	[DD] [nvarchar](2) NULL,
	[UPDATE_DATE] [nvarchar](10) NULL,
	[UPDATE_TIME] [nvarchar](10) NULL,
	[USER_ID] [nvarchar](20) NULL,
 CONSTRAINT [PK_SUMMARY_TAX_406] PRIMARY KEY CLUSTERED 
(
	[YYYY] ASC,
	[MM] ASC,
	[HOSPITAL_CODE] ASC,
	[DOCTOR_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_REVENUE_REPORT]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VW_REVENUE_REPORT]
AS
SELECT     PM.YYYY, PM.MM, PM.TRANSACTION_DATE, PM.SUM_AMT, PM.SUM_DISC_AMT, PM.SUM_PREMIUM_AMT, PM.SUM_PATIENT_PAID_AMT, 
                      PM.DR_SUM_AMT, PM.DR_DISC_AMT, PM.DR_PREMIUM_AMT, PM.DR_CREDIT_AMT, PM.DR_DEBIT_AMT, PM.DR_NET_PAID_AMT, 
                      PM.DR_NET_TAX_406_AMT, PM.SUM_PAY_BY_CASH, PM.SUM_PAY_BY_AR, PM.DR_TAX_400, PM.DR_TAX_401, PM.DR_TAX_402, PM.DR_TAX_406,
                       PM.HP_SUM_AMT, PM.HP_DISC_AMT, PM.HP_PREMIUM_AMT, PM.HP_TAX, PM.GUARUNTEE_AMT, PM.GUARUNTEE_TAX, PM.HOSPITAL_CODE, 
                      PM.STATUS_MODIFY, PM.BATCH_NO, PM.PAYMENT_MODE_CODE, PM.REF_PAID_NO, PM.PAY_DATE, PM.RECEIPT_CHEQUE_DATE, 
                      PM.PAYMENT_TERM_DATE, PM.DR_STEP_AMT, PM.HP_STEP_AMT, PM.SUM_PREMIUM_REC_AMT, PM.CASH_AMT, PM.CASH_400, PM.CASH_401, 
                      PM.CASH_402, PM.CASH_406, PM.AR_AMT, PM.AR_400, PM.AR_401, PM.AR_402, PM.AR_406, PM.EX_400, PM.EX_401, PM.EX_402, PM.EX_406, 
                      PM.GDR_AMT, PM.GCR_AMT, PM.ABCR_AMT, PM.ABCR_402, PM.ABCR_406, ISNULL
                          ((SELECT     SUM(ED.AMOUNT) AS Expr1
                              FROM         dbo.TRN_EXPENSE_DETAIL AS ED LEFT OUTER JOIN
                                                    dbo.EXPENSE AS EX ON ED.EXPENSE_CODE = EX.CODE AND ED.HOSPITAL_CODE = EX.HOSPITAL_CODE
                              WHERE     (ED.HOSPITAL_CODE = PM.HOSPITAL_CODE) AND (ED.YYYY = PM.YYYY) AND (ED.MM = PM.MM) AND 
                                                    (ED.DOCTOR_CODE = PM.DOCTOR_CODE) AND (EX.ADJUST_TYPE NOT IN (''HP'', ''DR'', ''EX'')) AND (ED.EXPENSE_SIGN = ''-1'')
                              GROUP BY ED.DOCTOR_CODE), 0) AS EXCR_AMT, ISNULL
                          ((SELECT     SUM(ED.AMOUNT) AS Expr1
                              FROM         dbo.TRN_EXPENSE_DETAIL AS ED LEFT OUTER JOIN
                                                    dbo.EXPENSE AS EX ON ED.EXPENSE_CODE = EX.CODE AND ED.HOSPITAL_CODE = EX.HOSPITAL_CODE
                              WHERE     (ED.HOSPITAL_CODE = PM.HOSPITAL_CODE) AND (ED.YYYY = PM.YYYY) AND (ED.MM = PM.MM) AND 
                                                    (ED.DOCTOR_CODE = PM.DOCTOR_CODE) AND (EX.ADJUST_TYPE NOT IN (''HP'', ''DR'', ''EX'')) AND (ED.EXPENSE_SIGN = ''1'')
                              GROUP BY ED.DOCTOR_CODE), 0) AS EXDR_AMT, ISNULL
                          ((SELECT     SUM(ED.AMOUNT) AS Expr1
                              FROM         dbo.TRN_EXPENSE_DETAIL AS ED LEFT OUTER JOIN
                                                    dbo.EXPENSE AS EX ON ED.EXPENSE_CODE = EX.CODE AND ED.HOSPITAL_CODE = EX.HOSPITAL_CODE
                              WHERE     (ED.HOSPITAL_CODE = PM.HOSPITAL_CODE) AND (ED.YYYY = PM.YYYY) AND (ED.MM = PM.MM) AND 
                                                    (ED.DOCTOR_CODE = PM.DOCTOR_CODE) AND (EX.ADJUST_TYPE = ''HP'')
                              GROUP BY ED.DOCTOR_CODE), 0) AS GDR_402, ISNULL
                          ((SELECT     SUM(ED.AMOUNT) AS Expr1
                              FROM         dbo.TRN_EXPENSE_DETAIL AS ED LEFT OUTER JOIN
                                                    dbo.EXPENSE AS EX ON ED.EXPENSE_CODE = EX.CODE AND ED.HOSPITAL_CODE = EX.HOSPITAL_CODE
                              WHERE     (ED.HOSPITAL_CODE = PM.HOSPITAL_CODE) AND (ED.YYYY = PM.YYYY) AND (ED.MM = PM.MM) AND 
                                                    (ED.DOCTOR_CODE = PM.DOCTOR_CODE) AND (EX.ADJUST_TYPE = ''DR'')
                              GROUP BY ED.DOCTOR_CODE), 0) AS GDR_406, PM.TAX_406_DATE, PM.IS_HOLD, PM.SALARY, PM.POSITION_AMT, PM.PAYMENT_TYPE, 
                      dbo.DOCTOR.NAME_THAI AS DR_NAME_THAI, 
                      CASE WHEN DOCTOR.HOSPITAL_UNIT_CODE = '''' THEN DOCTOR.HOSPITAL_CODE ELSE DOCTOR.HOSPITAL_UNIT_CODE END AS HOSPITAL_UNIT_CODE,
                       CASE WHEN DOCTOR.HOSPITAL_UNIT_CODE = '''' THEN HP.DESCRIPTION_THAI ELSE DOCTOR.HOSPITAL_UNIT_CODE END AS HOSPITAL_UNIT_DESCRIPTION,
                       dbo.DOCTOR.DOCTOR_TYPE_CODE, dbo.DOCTOR_TYPE.DESCRIPTION AS DOCTOR_TYPE_DESCRIPTION, DF.NAME_THAI AS DF_NAME_THAI, 
                      dbo.DOCTOR.DOCTOR_PROFILE_CODE, PM.DOCTOR_CODE, dbo.DOCTOR.GUARANTEE_DR_CODE, 
                      CASE WHEN DOCTOR.GUARANTEE_DR_CODE = '''' THEN 0.0 ELSE CASE WHEN SUBSTRING(DOCTOR.DOCTOR_TYPE_CODE, 1, 1) 
                      = ''G'' THEN CASE WHEN DOCTOR.DOCTOR_TYPE_CODE = ''GDM'' THEN ISNULL
                          ((SELECT     TOP 1 GUARANTEE_AMOUNT
                              FROM         STP_GUARANTEE
                              WHERE     MM = PM.MM AND YYYY = PM.YYYY AND GUARANTEE_DR_CODE = DOCTOR.CODE AND ACTIVE = ''1''), 0.0) ELSE ISNULL
                          ((SELECT     SUM(GUARANTEE_AMOUNT)
                              FROM         STP_GUARANTEE
                              WHERE     MM = PM.MM AND YYYY = PM.YYYY AND GUARANTEE_DR_CODE = DOCTOR.CODE AND ACTIVE = ''1''
                              GROUP BY GUARANTEE_DR_CODE), 0.0) END ELSE 0.0 END END AS SUM_GUARANTEE_AMOUNT, ISNULL
                          ((SELECT     SUM(GUARANTEE_EXCLUDE_AMOUNT) AS Expr1
                              FROM         dbo.STP_GUARANTEE
                              WHERE     (MM = PM.MM) AND (YYYY = PM.YYYY) AND (GUARANTEE_DR_CODE = dbo.DOCTOR.GUARANTEE_DR_CODE) AND (ACTIVE = ''1'')
                              GROUP BY GUARANTEE_DR_CODE), 0.0) AS SUM_GUARANTEE_EXCLUDE_AMOUNT, ISNULL
                          ((SELECT     SUM(GUARANTEE_FIX_AMOUNT) AS Expr1
                              FROM         dbo.STP_GUARANTEE AS STP_GUARANTEE_2
                              WHERE     (MM = PM.MM) AND (YYYY = PM.YYYY) AND (GUARANTEE_DR_CODE = dbo.DOCTOR.GUARANTEE_DR_CODE) AND (ACTIVE = ''1'')
                              GROUP BY GUARANTEE_DR_CODE), 0.0) AS SUM_GUARANTEE_FIX_AMOUNT, ISNULL
                          ((SELECT     SUM(GUARANTEE_INCLUDE_AMOUNT) AS Expr1
                              FROM         dbo.STP_GUARANTEE AS STP_GUARANTEE_1
                              WHERE     (MM = PM.MM) AND (YYYY = PM.YYYY) AND (GUARANTEE_DR_CODE = dbo.DOCTOR.GUARANTEE_DR_CODE) AND (ACTIVE = ''1'')
                              GROUP BY GUARANTEE_DR_CODE), 0.0) AS SUM_GUARANTEE_INCLUDE_AMOUNT, ISNULL
                          ((SELECT     SUM(DR_AMT) AS Expr1
                              FROM         dbo.TRN_DAILY
                              WHERE     (MM = PM.MM) AND (YYYY = PM.YYYY) AND (DOCTOR_CODE = PM.DOCTOR_CODE) AND (PAY_BY_AR = ''Y'')
                              GROUP BY DOCTOR_CODE), 0.0) AS SUM_DR_AMT_BY_AR, ISNULL
                          ((SELECT     SUM(DR_AMT) AS Expr1
                              FROM         dbo.TRN_DAILY AS TRN_DAILY_1
                              WHERE     (MM = PM.MM) AND (YYYY = PM.YYYY) AND (DOCTOR_CODE = PM.DOCTOR_CODE) AND (VERIFY_DATE BETWEEN 
                                                    PM.YYYY + PM.MM + ''01'' AND PM.YYYY + PM.MM + ''31'')
                              GROUP BY DOCTOR_CODE), 0.0) AS SUM_DR_AMT_BY_VERIFY_DATE
FROM         dbo.PAYMENT_MONTHLY AS PM LEFT OUTER JOIN
                      dbo.DOCTOR ON PM.DOCTOR_CODE = dbo.DOCTOR.CODE AND PM.HOSPITAL_CODE = dbo.DOCTOR.HOSPITAL_CODE LEFT OUTER JOIN
                      dbo.DOCTOR_TYPE ON dbo.DOCTOR.DOCTOR_TYPE_CODE = dbo.DOCTOR_TYPE.CODE AND 
                      PM.HOSPITAL_CODE = dbo.DOCTOR_TYPE.HOSPITAL_CODE LEFT OUTER JOIN
                      dbo.HOSPITAL AS HP ON dbo.DOCTOR.HOSPITAL_CODE = HP.CODE AND PM.HOSPITAL_CODE = HP.CODE LEFT OUTER JOIN
                      dbo.DOCTOR_PROFILE AS DF ON dbo.DOCTOR.DOCTOR_PROFILE_CODE = DF.CODE AND dbo.DOCTOR.HOSPITAL_CODE = DF.HOSPITAL_CODE
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[24] 4[4] 2[53] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "PM"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 250
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DOCTOR"
            Begin Extent = 
               Top = 114
               Left = 38
               Bottom = 222
               Right = 255
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DOCTOR_TYPE"
            Begin Extent = 
               Top = 222
               Left = 38
               Bottom = 330
               Right = 232
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "HP"
            Begin Extent = 
               Top = 330
               Left = 38
               Bottom = 438
               Right = 265
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DF"
            Begin Extent = 
               Top = 438
               Left = 38
               Bottom = 546
               Right = 254
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_REVENUE_REPORT'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_REVENUE_REPORT'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_REVENUE_REPORT'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VM_SUM_GUARANTEE]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VM_SUM_GUARANTEE]
AS
SELECT     HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM, SUM(SUM_HP_OVER_AMOUNT) AS SUM_HP_OVER_AMOUNT, SUM(SUM_TAX_406) 
                      AS SUM_TAX_406, SUM(SUM_TAX_402) AS SUM_TAX_402, SUM(SUM_TAX_400) AS SUM_TAX_400
FROM         dbo.STP_GUARANTEE
WHERE     (GUARANTEE_TYPE_CODE = ''DLY'')
GROUP BY HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM
UNION
SELECT DISTINCT HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM, SUM_HP_OVER_AMOUNT, SUM_TAX_406, SUM_TAX_402, SUM_TAX_400
FROM         dbo.STP_GUARANTEE AS STP_GUARANTEE_1
WHERE     (GUARANTEE_TYPE_CODE LIKE ''M%'')
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 3
   End
   Begin DiagramPane = 
      PaneHidden = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 5
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VM_SUM_GUARANTEE'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VM_SUM_GUARANTEE'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_PROCESS_GUARANTEE]'))
EXEC dbo.sp_executesql @statement = N'create view [dbo].[VW_PROCESS_GUARANTEE] as
SELECT     HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM, SUM(GUARANTEE_AMOUNT) AS GUARANTEE_AMOUNT, SUM(GUARANTEE_FIX_AMOUNT) 
                     AS GUARANTEE_FIX_AMOUNT, SUM(GUARANTEE_INCLUDE_AMOUNT) AS GUARANTEE_INCLUDE_AMOUNT, SUM(GUARANTEE_EXCLUDE_AMOUNT) 
                     AS GUARANTEE_EXCLUDE_AMOUNT, SUM(SUM_HP_OVER_AMOUNT) AS SUM_HP_OVER_AMOUNT, SUM(SUM_TAX_406) AS SUM_TAX_406, 
                     SUM(SUM_TAX_402) AS SUM_TAX_402, SUM(SUM_TAX_400) AS SUM_TAX_400
FROM         dbo.STP_GUARANTEE
WHERE     (GUARANTEE_TYPE_CODE = ''DLY'')
GROUP BY HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM
UNION
SELECT DISTINCT 
                     HOSPITAL_CODE, GUARANTEE_DR_CODE, YYYY, MM, GUARANTEE_AMOUNT, GUARANTEE_FIX_AMOUNT, GUARANTEE_INCLUDE_AMOUNT, 
                     GUARANTEE_EXCLUDE_AMOUNT, SUM_HP_OVER_AMOUNT, SUM_TAX_406, SUM_TAX_402, SUM_TAX_400
FROM         dbo.STP_GUARANTEE AS STP_GUARANTEE_1
WHERE     (GUARANTEE_TYPE_CODE = ''MLY'')
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_ALL_SUMMARY_MONTHLY]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VW_ALL_SUMMARY_MONTHLY]
AS
SELECT     dbo.BATCH.YYYY, dbo.BATCH.MM, dbo.BATCH.PAYMENT_DATE AS TRANSACTION_DATE, ISNULL(dbo.SUMMARY_MONTHLY.SUM_AMT, 0) 
                      AS SUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.SUM_DISC_AMT, 0) AS SUM_DISC_AMT, ISNULL(dbo.SUMMARY_MONTHLY.SUM_PREMIUM_AMT, 
                      0) AS SUM_PREMIUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.SUM_PATIENT_PAID_AMT, 0) AS SUM_PATIENT_PAID_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.DR_SUM_AMT, 0) AS DR_SUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.DR_DISC_AMT, 0) AS DR_DISC_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.DR_PREMIUM_AMT, 0) AS DR_PREMIUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.DR_CREDIT_AMT, 0) 
                      AS DR_CREDIT_AMT, ISNULL(dbo.SUMMARY_MONTHLY.DR_DEBIT_AMT, 0) AS DR_DEBIT_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.DR_NET_PAID_AMT, 0) AS DR_NET_PAID_AMT, ISNULL(dbo.SUMMARY_MONTHLY.SUM_PAY_BY_CASH, 0) 
                      AS SUM_PAY_BY_CASH, ISNULL(dbo.SUMMARY_MONTHLY.SUM_PAY_BY_AR, 0) AS SUM_PAY_BY_AR, 
                      ISNULL(dbo.SUMMARY_MONTHLY.DR_TAX_400, 0) AS DR_TAX_400, ISNULL(dbo.SUMMARY_MONTHLY.DR_TAX_401, 0) AS DR_TAX_401, 
                      ISNULL(dbo.SUMMARY_MONTHLY.DR_TAX_402, 0) AS DR_TAX_402, ISNULL(dbo.SUMMARY_MONTHLY.DR_TAX_406, 0) AS DR_TAX_406, 
                      ISNULL(dbo.SUMMARY_MONTHLY.HP_SUM_AMT, 0) AS HP_SUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.HP_DISC_AMT, 0) AS HP_DISC_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.HP_PREMIUM_AMT, 0) AS HP_PREMIUM_AMT, ISNULL(dbo.SUMMARY_MONTHLY.HP_TAX, 0) AS HP_TAX, 
                      ISNULL(dbo.SUMMARY_MONTHLY.GUARUNTEE_AMT, 0) AS GUARUNTEE_AMT, ISNULL(dbo.SUMMARY_MONTHLY.GUARUNTEE_TAX, 0) 
                      AS GUARUNTEE_TAX, ISNULL(dbo.DOCTOR.HOSPITAL_CODE, N'''') AS HOSPITAL_CODE, ISNULL(dbo.DOCTOR.CODE, N'''') AS DOCTOR_CODE, 
                      ISNULL(dbo.SUMMARY_MONTHLY.UPDATE_DATE, N'''') AS UPDATE_DATE, ISNULL(dbo.SUMMARY_MONTHLY.UPDATE_TIME, N'''') AS UPDATE_TIME, 
                      ISNULL(dbo.SUMMARY_MONTHLY.USER_ID, N'''') AS USER_ID, ISNULL(dbo.SUMMARY_MONTHLY.STATUS_MODIFY, N'''') AS STATUS_MODIFY, 
                      ISNULL(dbo.SUMMARY_MONTHLY.BATCH_NO, N'''') AS BATCH_NO, ISNULL(dbo.DOCTOR.CODE, N'''') AS OLD_DOCTOR_CODE, 
                      ISNULL(dbo.SUMMARY_MONTHLY.REMARK, N'''') AS REMARK, ISNULL(dbo.DOCTOR.PAYMENT_MODE_CODE, N'''') AS PAYMENT_MODE_CODE, 
                      ISNULL(dbo.DOCTOR.BANK_ACCOUNT_NO, N'''') AS REF_PAID_NO, ISNULL(dbo.SUMMARY_MONTHLY.PAY_DATE, N'''') AS PAY_DATE, 
                      ISNULL(dbo.SUMMARY_MONTHLY.PAYMENT_TERM_DATE, N'''') AS PAYMENT_TERM_DATE, ISNULL(dbo.SUMMARY_MONTHLY.DR_STEP_AMT, 0) 
                      AS DR_STEP_AMT, ISNULL(dbo.SUMMARY_MONTHLY.HP_STEP_AMT, 0) AS HP_STEP_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.SUM_PREMIUM_REC_AMT, 0) AS SUM_PREMIUM_REC_AMT, 
                      ISNULL(dbo.SUMMARY_MONTHLY.EDIT_DATE_OF_PAY_DATE, 0) AS EDIT_DATE_OF_PAY_DATE, ISNULL(dbo.SUMMARY_MONTHLY.CASH_AMT, 0) 
                      AS CASH_AMT, ISNULL(dbo.SUMMARY_MONTHLY.CASH_400, 0) AS CASH_400, ISNULL(dbo.SUMMARY_MONTHLY.CASH_401, 0) AS CASH_401, 
                      ISNULL(dbo.SUMMARY_MONTHLY.CASH_402, 0) AS CASH_402, ISNULL(dbo.SUMMARY_MONTHLY.CASH_406, 0) AS CASH_406, 
                      ISNULL(dbo.SUMMARY_MONTHLY.AR_AMT, 0) AS AR_AMT, ISNULL(dbo.SUMMARY_MONTHLY.AR_400, 0) AS AR_400, 
                      ISNULL(dbo.SUMMARY_MONTHLY.AR_401, 0) AS AR_401, ISNULL(dbo.SUMMARY_MONTHLY.AR_402, 0) AS AR_402, 
                      ISNULL(dbo.SUMMARY_MONTHLY.AR_406, 0) AS AR_406, ISNULL(dbo.TRN_EXPENSE_HEADER.DEBIT_TOTAL_AMOUNT, 0) AS EXDR_AMT, 
                      ISNULL(dbo.TRN_EXPENSE_HEADER.CREDIT_TOTAL_AMOUNT, 0) AS EXCR_AMT, 
                      ISNULL(dbo.TRN_EXPENSE_HEADER.DEBIT_400_AMOUNT - dbo.TRN_EXPENSE_HEADER.CREDIT_400_AMOUNT, 0) AS EX_400, 
                      ISNULL(dbo.TRN_EXPENSE_HEADER.DEBIT_401_AMOUNT - dbo.TRN_EXPENSE_HEADER.CREDIT_401_AMOUNT, 0) AS EX_401, 
                      ISNULL(dbo.TRN_EXPENSE_HEADER.DEBIT_402_AMOUNT - dbo.TRN_EXPENSE_HEADER.CREDIT_402_AMOUNT, 0) AS EX_402, 
                      ISNULL(dbo.TRN_EXPENSE_HEADER.DEBIT_406_AMOUNT - dbo.TRN_EXPENSE_HEADER.CREDIT_406_AMOUNT, 0) AS EX_406, 
                      ISNULL(dbo.SUMMARY_GUARANTEE.SUM_TAX_400 + dbo.SUMMARY_GUARANTEE.SUM_TAX_402 + dbo.SUMMARY_GUARANTEE.SUM_TAX_406, 0) 
                      AS GDR_AMT, ISNULL(dbo.SUMMARY_GUARANTEE.SUM_TAX_402, 0) AS GDR_402, ISNULL(dbo.SUMMARY_GUARANTEE.SUM_TAX_406, 0) 
                      AS GDR_406, 0 AS GCR_AMT, 0 AS ABCR_AMT, 0 AS ABCR_402, 0 AS ABCR_406, dbo.DOCTOR.IS_HOLD, ISNULL(dbo.DOCTOR.SALARY, 0) 
                      AS SALARY, ISNULL(dbo.DOCTOR.POSITION_AMT, 0) AS POSITION_AMT
FROM         dbo.DOCTOR LEFT OUTER JOIN
                      dbo.BATCH ON dbo.DOCTOR.HOSPITAL_CODE = dbo.BATCH.HOSPITAL_CODE LEFT OUTER JOIN
                      dbo.SUMMARY_MONTHLY ON dbo.DOCTOR.CODE = dbo.SUMMARY_MONTHLY.DOCTOR_CODE AND 
                      dbo.SUMMARY_MONTHLY.MM = dbo.BATCH.MM AND dbo.SUMMARY_MONTHLY.YYYY = dbo.BATCH.YYYY AND 
                      dbo.DOCTOR.HOSPITAL_CODE = dbo.SUMMARY_MONTHLY.HOSPITAL_CODE LEFT OUTER JOIN
                      dbo.TRN_EXPENSE_HEADER ON dbo.DOCTOR.CODE = dbo.TRN_EXPENSE_HEADER.DOCTOR_CODE AND 
                      dbo.TRN_EXPENSE_HEADER.MM = dbo.BATCH.MM AND dbo.TRN_EXPENSE_HEADER.YYYY = dbo.BATCH.YYYY AND 
                      dbo.DOCTOR.HOSPITAL_CODE = dbo.TRN_EXPENSE_HEADER.HOSPITAL_CODE LEFT OUTER JOIN
                      dbo.SUMMARY_GUARANTEE ON dbo.DOCTOR.CODE = dbo.SUMMARY_GUARANTEE.DOCTOR_CODE AND 
                      dbo.DOCTOR.HOSPITAL_CODE = dbo.SUMMARY_GUARANTEE.HOSPITAL_CODE AND dbo.SUMMARY_GUARANTEE.MM = dbo.BATCH.MM AND 
                      dbo.SUMMARY_GUARANTEE.YYYY = dbo.BATCH.YYYY
WHERE     (dbo.BATCH.CLOSE_DATE = '''' OR
                      dbo.BATCH.CLOSE_DATE IS NULL) AND (dbo.TRN_EXPENSE_HEADER.BATCH_NO IS NULL OR
                      dbo.TRN_EXPENSE_HEADER.BATCH_NO = '''') AND (dbo.DOCTOR.ACTIVE = ''1'')
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[22] 4[30] 2[15] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[43] 2[35] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 2
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "DOCTOR"
            Begin Extent = 
               Top = 174
               Left = 300
               Bottom = 309
               Right = 532
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BATCH"
            Begin Extent = 
               Top = 229
               Left = 649
               Bottom = 346
               Right = 844
            End
            DisplayFlags = 280
            TopColumn = 2
         End
         Begin Table = "SUMMARY_MONTHLY"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 242
               Right = 259
            End
            DisplayFlags = 280
            TopColumn = 21
         End
         Begin Table = "TRN_EXPENSE_HEADER"
            Begin Extent = 
               Top = 5
               Left = 398
               Bottom = 153
               Right = 611
            End
            DisplayFlags = 280
            TopColumn = 5
         End
         Begin Table = "SUMMARY_GUARANTEE"
            Begin Extent = 
               Top = 51
               Left = 546
               Bottom = 218
               Right = 794
            End
            DisplayFlags = 280
            TopColumn = 28
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 67
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         W' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_ALL_SUMMARY_MONTHLY'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'idth = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      PaneHidden = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 2460
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_ALL_SUMMARY_MONTHLY'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_ALL_SUMMARY_MONTHLY'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_SUM_SUMMARY_DAILY]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VW_SUM_SUMMARY_DAILY]
AS
SELECT     TOP (100) PERCENT DOCTOR_CODE, SUM(AMOUNT_AFT_DISCOUNT) AS sinv_amt, SUM(AMOUNT_OF_DISCOUNT) AS sinv_discount_amt, SUM(DR_AMT) 
                      AS sDr_Amt, SUM(DR_TAX_400) AS sDr_Amt_400, SUM(DR_TAX_401) AS sDr_Amt_401, SUM(DR_TAX_402) AS sDr_Amt_402, SUM(DR_TAX_406) AS sDr_Amt_406, 
                      SUM(DR_PREMIUM) AS sDr_Premium, SUM(HP_AMT) AS sHp_Amt, SUM(HP_PREMIUM) AS sHp_Premium, SUM(HP_TAX) AS sHp_Tax, SUM(0) AS sRec_Amt, SUM(0) 
                      AS sRec_Premium_Amt, SUM(PREMIUM_REC_AMT) AS sPremium_Rec_Amt, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_AMT END) AS sDr_Amt_CASH, 
                      SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_AMT ELSE 0 END) AS sDr_Amt_AR, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_AMT END) AS sCash_Amt, 
                      SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_TAX_400 END) AS sCash_400, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_TAX_401 END) 
                      AS sCash_401, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_TAX_402 END) AS sCash_402, 
                      SUM(CASE PAY_BY_AR WHEN ''Y'' THEN 0 ELSE DR_TAX_406 END) AS sCash_406, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_AMT ELSE 0 END) AS sAr_Amt, 
                      SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_TAX_400 ELSE 0 END) AS sAr_400, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_TAX_401 ELSE 0 END) AS sAr_401, 
                      SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_TAX_402 ELSE 0 END) AS sAr_402, SUM(CASE PAY_BY_AR WHEN ''Y'' THEN DR_TAX_406 ELSE 0 END) 
                      AS sAr_406
FROM         dbo.SUMMARY_DAILY
WHERE     (HOSPITAL_CODE = ''201'') AND (BATCH_NO IS NULL OR
                      BATCH_NO = '''') AND (YYYY = ''2008'') AND (MM = ''02'') AND (DOCTOR_CODE IS NOT NULL) AND (DOCTOR_CODE <> '''') AND (ACTIVE = ''1'') AND 
                      (COMPUTE_DAILY_DATE IS NOT NULL) AND (COMPUTE_DAILY_DATE <> '''') AND (PAY_BY_CASH = ''Y'') OR
                      (HOSPITAL_CODE = ''201'') AND (BATCH_NO IS NULL OR
                      BATCH_NO = '''') AND (YYYY = ''2008'') AND (MM = ''02'') AND (DOCTOR_CODE IS NOT NULL) AND (DOCTOR_CODE <> '''') AND (ACTIVE = ''1'') AND 
                      (COMPUTE_DAILY_DATE IS NOT NULL) AND (COMPUTE_DAILY_DATE <> '''') AND (PAY_BY_AR = ''Y'') OR
                      (HOSPITAL_CODE = ''201'') AND (BATCH_NO IS NULL OR
                      BATCH_NO = '''') AND (YYYY = ''2008'') AND (MM = ''02'') AND (DOCTOR_CODE IS NOT NULL) AND (DOCTOR_CODE <> '''') AND (ACTIVE = ''1'') AND 
                      (COMPUTE_DAILY_DATE IS NOT NULL) AND (COMPUTE_DAILY_DATE <> '''') AND (PAY_BY_DOCTOR = ''Y'') OR
                      (HOSPITAL_CODE = ''201'') AND (BATCH_NO IS NULL OR
                      BATCH_NO = '''') AND (YYYY = ''2008'') AND (MM = ''02'') AND (DOCTOR_CODE IS NOT NULL) AND (DOCTOR_CODE <> '''') AND (ACTIVE = ''1'') AND 
                      (COMPUTE_DAILY_DATE IS NOT NULL) AND (COMPUTE_DAILY_DATE <> '''') AND (PAY_BY_PAYOR = ''Y'') OR
                      (HOSPITAL_CODE = ''201'') AND (BATCH_NO IS NULL OR
                      BATCH_NO = '''') AND (YYYY = ''2008'') AND (MM = ''02'') AND (DOCTOR_CODE IS NOT NULL) AND (DOCTOR_CODE <> '''') AND (ACTIVE = ''1'') AND 
                      (COMPUTE_DAILY_DATE IS NOT NULL) AND (COMPUTE_DAILY_DATE <> '''') AND (PAY_BY_CASH_AR = ''Y'')
GROUP BY DOCTOR_CODE
ORDER BY DOCTOR_CODE
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[7] 4[13] 2[45] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "SUMMARY_DAILY"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 123
               Right = 354
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 28
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 14
         Column = 4605
         Alias = 1770
         Table = 1560
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 885
         SortOrder = 1080
         GroupBy = 840
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_SUM_SUMMARY_DAILY'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'VW_SUM_SUMMARY_DAILY'

